Ведение заказа в запросе MySQL "IN"

У меня есть следующая таблица

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)

Ответ 1

Как говорится в другом ответе: в запросе, который вы опубликовали, ничего не говорится о том, какой порядок вы хотите получить, и какие результаты вы хотели бы получить.

Чтобы заказать ваши результаты, я бы использовал ORDER BY FIELD():

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

Список аргументов для FIELD может быть переменной длины.

Ответ 2

Значения в предикате 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');

Обратите внимание, что аргумент списка FIND_IN_SET() не является списком длины переменной, как аргументы IN(). Он должен быть строковым литералом или SET.

Отвечайте на вопросы о производительности: мне тоже интересно, поэтому я попытался использовать методы FIND_IN_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)

Заключение: при ограниченном тестировании нет большого преимущества для любого метода.