mysql - 为什么 MySQL 不按照 IN 子句中出现的顺序返回行？

Question

此查询要求 MySQL 返回product 表中的项目。我希望结果按 IN 子句中出现的顺序返回。

select id, price 
from product 
where id IN (5552902, 5552737, 5552887, 5549436, 5552291, 5552897, 
             5550834, 5552794, 5548666, 5552621);

+---------+----------+
| id      | price    |
+---------+----------+
| 5548666 | 919.0000 |
| 5549436 | 939.0000 |
| 5550834 | 919.0000 |
| 5552291 | 939.0000 |
| 5552621 | 829.0000 |
| 5552737 | 999.0000 |
| 5552794 | 919.0000 |
| 5552887 | 999.0000 |
| 5552897 | 939.0000 |
| 5552902 | 999.0000 |
+---------+----------+

出于某种原因，MySQL 返回结果时不考虑顺序，我必须使用 ORDER BY Price DESC 才能获得所需的结果。

select id, price 
from product 
where id IN (5552902, 5552737, 5552887, 5549436, 5552291, 
             5552897, 5550834, 5552794, 5548666, 5552621) 
ORDER BY price DESC;

+---------+----------+
| id      | price    |
+---------+----------+
| 5552902 | 999.0000 |
| 5552887 | 999.0000 |
| 5552737 | 999.0000 |
| 5552291 | 939.0000 |
| 5549436 | 939.0000 |
| 5552897 | 939.0000 |
| 5550834 | 919.0000 |
| 5552794 | 919.0000 |
| 5548666 | 919.0000 |
| 5552621 | 829.0000 |
+---------+----------+

MySQL 版本是5.6。我想知道为什么 MySQL 不按“正确”的顺序返回项目。

Answer 1

如果您想保留 in Clause 中的顺序，您可以使用 order by field ，如

select 
id, price from product 
where id IN (5552902, 5552737, 5552887, 5549436, 5552291, 5552897, 5550834, 5552794, 5548666, 5552621)
order by field(id,5552902, 5552737, 5552887, 5549436, 5552291, 5552897, 5550834, 5552794, 5548666, 5552621)
;