代码之家 › 专栏 › 技术社区 › msp albert muda

在mysql“in”查询中维护顺序

mysql sql

msp albert muda · 技术社区 · 17 年前

我有下表

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;

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

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

然后我得到以下结果

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

如您所见,结果是按ID排序的。我试图实现的是按我在查询中提供的顺序排列结果。在这个例子中,它应该返回

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

2 回复 | 直到 17 年前

Ty W 17 年前

另一个答案提到:您发布的查询与您想要的结果的顺序无关,只是与您想要得到的结果无关。

要订购结果,我将使用order by field():

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

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

Bill Karwin 17 年前

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

通常,任何SQL查询的顺序都是任意的除非指定订单时 ORDER BY 条款。

可以使用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');

注意,list参数 (查找) 不是可变长度的列表,就像 () .它必须是字符串或 SET .

关于表现的问题:我也很好奇,所以我尝试了两种方法 (查找) 和 FIELD() 针对StackOverflow数据的副本的方法:

在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)

在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)

结论:在有限的检测条件下,两种方法都没有很大的优势。