在“MySQL”查询中保持顺序

I have the following table

我有下面这张桌子

DROP TABLE IF EXISTS `test`.`foo`;
CREATE TABLE  `test`.`foo` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Then I try to get records based on the primary key

然后我尝试根据主键获取记录

SELECT * FROM foo f where f.id IN (2, 3, 1);

I then get the following result

然后得到以下结果

+----+--------+
| id | name   |
+----+--------+
|  1 | first  |
|  2 | second |
|  3 | third  |
+----+--------+
3 rows in set (0.00 sec)

As one can see, the result is ordered by id. What I'm trying to achieve is to get the results ordered in the sequence I'm providing in the query. Given this example it should return

可以看到，结果是按id排序的，我要实现的是按照查询中提供的顺序来排序结果。在这个例子中，它应该返回。

+----+--------+
| id | name   |
+----+--------+
|  2 | second |
|  3 | third  |
|  1 | first  |
+----+--------+
3 rows in set (0.00 sec)

2 个解决方案

#1

As the other answer mentions: the query you posted has nothing about what order you'd like your results, just which results you'd like to get.

正如其他答案所提到的:您发布的查询没有关于您希望结果的顺序，只是您希望得到的结果。

To order your results, I would use ORDER BY FIELD():

为了给您的结果排序，我将使用order BY FIELD():

SELECT * FROM foo f where f.id IN (2, 3, 1)
ORDER BY FIELD(f.id, 2, 3, 1);

The argument list to FIELD can be variable length.

到字段的参数列表可以是可变长度。

#2

The values in an IN() predicate are considered to be a set, and the result returned by an SQL query has no way to automatically infer order from that set.

in()谓词中的值被认为是一个集合，SQL查询返回的结果无法自动从该集合推断顺序。

In general, the order of any SQL query is arbitrary unless you specify an order with an ORDER BY clause.

通常，任何SQL查询的顺序都是任意的，除非您使用order BY子句指定顺序。

You can use a MySQL function FIND_IN_SET() to do what you want:

您可以使用MySQL函数FIND_IN_SET()来完成所需的工作:

SELECT * FROM foo f where f.id IN (2, 3, 1)
ORDER BY FIND_IN_SET(f.id, '2,3,1');

Note that the list argument to FIND_IN_SET() isn't a variable length list like the arguments of IN(). It has to be a string literal or a SET.

注意，FIND_IN_SET()的列表参数不是一个变量长度列表，不像IN()的参数。它必须是字符串文字或集合。

Re questions about performance: I'm curious too, so I tried both FIND_IN_SET() and FIELD() methods against my copy of the * data:

关于性能的问题:我也很好奇，所以我对*数据的拷贝尝试了FIND_IN_SET()和FIELD()方法:

With no index on VoteTypeId:

没有关于VoteTypeId的索引:

SELECT * FROM Votes ORDER BY FIND_IN_SET(VoteTypeId, '13,1,12,2,11,3,10,4,9,5,8,6,7');

3618992 rows in set (31.26 sec)
3618992 rows in set (29.67 sec)
3618992 rows in set (28.52 sec)

SELECT * FROM Votes ORDER BY FIELD(VoteTypeId, 13,1,12,2,11,3,10,4,9,5,8,6,7);

3618992 rows in set (37.30 sec)
3618992 rows in set (49.65 sec)
3618992 rows in set (41.69 sec)

With an index on VoteTypeId:

有关于VoteTypeId的索引:

SELECT * FROM Votes ORDER BY FIND_IN_SET(VoteTypeId, '13,1,12,2,11,3,10,4,9,5,8,6,7');

3618992 rows in set (14.71 sec)
3618992 rows in set (14.81 sec)
3618992 rows in set (25.80 sec)

SELECT * FROM Votes ORDER BY FIELD(VoteTypeId, 13,1,12,2,11,3,10,4,9,5,8,6,7);

3618992 rows in set (19.03 sec)
3618992 rows in set (14.59 sec)
3618992 rows in set (14.43 sec)

Conclusion: with limited testing, there is no great advantage to either method.

结论:在有限的测试条件下，两种方法都没有太大的优势。

#1