按特定的ID值排序

Is it possible to sort in mysql by "order by" using predefined set of column values (ID) like: order by (ID=1,5,4,3) so I would get record 1, 5, 4, 3 in that order out?

在mysql中，是否可以使用预定义的列值集(ID)如:order by (ID=1、5、4、3)按“order by”进行排序，以便按此顺序得到记录1、5、4、3 ?

UPDATE: About abusing mysql ;-) I have to explain why I need this...

更新:关于滥用mysql;-)我必须解释为什么我需要这个…

I want my records change sort randomly every 5 minutes. I have a cron task to do the update table to put different, random sort order in it. There is just one problem! PAGINATION. I will have a visitor who comes to my page and I give him first 20 results. He will wait 6 minutes and go to page 2 and he will have wrong results as the sort order had allready changed.

我想让我的记录每5分钟随机改变排序。我有一个cron任务来执行更新表，以在其中放置不同的、随机的排序顺序。只有一个问题!分页。我会有一个访客来到我的页面，我给他前20个结果。他会等6分钟，然后到第二页，他会有错误的结果，因为排序顺序已经改变。

So I thought that if he comes to my site I put all the ID's to a session and when he is in page 2 he get's the correct records out even if the sorting allready changed.

所以我想，如果他来到我的网站，我会把所有的ID放到一个会话中，当他在第2页时，他会得到正确的记录，即使排序已经改变。

Is there any other way, better, to do this?

还有其他更好的办法吗?

5 个解决方案

#1

128

You can use ORDER BY and FIELD function. See http://lists.mysql.com/mysql/209784

可以使用ORDER BY和FIELD函数。参见http://lists.mysql.com/mysql/209784

SELECT * FROM table ORDER BY FIELD(ID,1,5,4,3)

It uses Field() function, Which "Returns the index (position) of str in the str1, str2, str3, ... list. Returns 0 if str is not found" according to the documentation. So actually you sort the result set by the return value of this function which is the index of the field value in the given set.

它使用Field()函数，该函数“返回str1、str2、str3、…列表。如果在文档中没有找到str，则返回0。实际上，你根据这个函数的返回值对结果集进行排序这是给定集合中字段值的索引。

#2

You should be able to use CASE for this:

您应该能够使用这个案例:

ORDER BY CASE id
  WHEN 1 THEN 1
  WHEN 5 THEN 2
  WHEN 4 THEN 3
  WHEN 3 THEN 4
  ELSE 5
END

#3

On the official documentation for mysql about ORDER BY, someone has posted that you can use FIELD for this matter, like this:

关于mysql的官方文档，有人已经发布了你可以使用字段来处理这个问题，比如:

SELECT * FROM table ORDER BY FIELD(id,1,5,4,3)

This is untested code that in theory should work.

这是未经测试的代码，理论上应该可以工作。

#4

There's another way to solve this. Add a separate table, something like this:

有另一种方法可以解决这个问题。添加一个单独的表，如下所示:

CREATE TABLE `new_order` (
  `my_order` BIGINT(20) UNSIGNED NOT NULL,
  `my_number` BIGINT(20) NOT NULL,
  PRIMARY KEY (`my_order`),
  UNIQUE KEY `my_number` (`my_number`)
) ENGINE=INNODB;

This table will now be used to define your own order mechanism.

现在将使用该表来定义您自己的订单机制。

Add your values in there:

在其中添加您的值:

my_order | my_number
---------+----------
       1 |         1
       2 |         5
       3 |         4
       4 |         3

...and then modify your SQL statement while joining this new table.

…然后在加入这个新表时修改SQL语句。

SELECT *
FROM your_table AS T1
INNER JOIN new_order AS T2 on T1.id = T2.my_number
WHERE ....whatever...
ORDER BY T2.my_order;

This solution is slightly more complex than other solutions, but using this you don't have to change your SELECT-statement whenever your order criteriums change - just change the data in the order table.

这个解决方案比其他解决方案稍微复杂一些，但是使用这个解决方案，您不必在订单标准发生变化时更改选择语句——只需更改订单表中的数据即可。

#5

SELECT * FROM table ORDER BY id='8' DESC, id='5' DESC, id='4' DESC, id='3' DESC

根据id='8' DESC, id='5' DESC, id='4' DESC, id='3' DESC从表序中选择*

If I had 10 registries for example, this way the ID 1, 5, 4 and 3 will appears first, the others registries will appears next.

例如，如果我有10个注册中心，那么ID 1、5、4和3将首先出现，其他注册中心将随后出现。

Normal exibition 1 2 3 4 5 6 7 8 9 10

正常现象1 2 3 4 5 6 7 8 9 10

With this way

用这种方式

8 5 4 3 1 2 6 7 9 10

#1

128