MySQL中select filed from table where field in (....)语句的排序问题

select filed from table where field in (....)语句的排序问题

执行语句发现返回的结果并非按照in列表中的顺序排序，具体可见如下示例：

示例表：
mysql> show create table test_select_in\G
*************************** 1. row ***************************
       Table: test_select_in
Create Table: CREATE TABLE `test_select_in` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `number` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

表中的数据：
mysql> select * from test_select_in;
+----+--------+
| id | number |
+----+--------+
|  1 |      2 |
|  2 |      3 |
|  3 |      5 |
|  4 |      8 |
|  5 |      7 |
|  6 |      1 |
|  7 |      9 |
|  8 |      6 |
+----+--------+
8 rows in set (0.00 sec)

执行select filed from table where field in (....)语句
mysql> select id from test_select_in where id in(8,7,6,5,4);
+----+
| id |
+----+
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
+----+
5 rows in set (0.00 sec)

mysql> select id,number from test_select_in where number in(9,6,1,7,8);
+----+--------+
| id | number |
+----+--------+
|  4 |      8 |
|  5 |      7 |
|  6 |      1 |
|  7 |      9 |
|  8 |      6 |
+----+--------+
5 rows in set (0.00 sec)
发现返回的结果并没有像想象的那样按照in列表中的顺序排序，而是按表中的自然顺序排序的。

解决办法：
利用field函数或者find_in_set函数

两个函数的作用：
FIELD(str,str1,str2,str3,...)
Returns the index (position) of str in the str1, str2, str3, ... list.
Returns 0 if str is not found.

FIND_IN_SET(str,strlist)
Returns a value in the range of 1 to N if the string str is in the
string list strlist consisting of N substrings.

语句改写后重新执行：

mysql> select id from test_select_in where id in(8,7,6,5,4) order by field(id,8,7,6,5,4);
+----+
| id |
+----+
|  8 |
|  7 |
|  6 |
|  5 |
|  4 |
+----+
5 rows in set (0.00 sec)

mysql> select id from test_select_in where id in(8,7,6,5,4) order by find_in_set(id,'8,7,6,5,4');
+----+
| id |
+----+
|  8 |
|  7 |
|  6 |
|  5 |
|  4 |
+----+
5 rows in set (0.00 sec)

mysql> select id,number from test_select_in where number in(9,6,1,7,8) order by field(number,9,6,1,7,8);
+----+--------+
| id | number |
+----+--------+
|  7 |      9 |
|  8 |      6 |
|  6 |      1 |
|  5 |      7 |
|  4 |      8 |
+----+--------+
5 rows in set (0.00 sec)


mysql> select id,number from test_select_in where number in(9,6,1,7,8) order by find_in_set(number,'9,6,1,7,8');
+----+--------+
| id | number |
+----+--------+
|  7 |      9 |
|  8 |      6 |
|  6 |      1 |
|  5 |      7 |
|  4 |      8 |
+----+--------+
5 rows in set (0.00 sec)

可以看到按照预期的顺序进行了输出。但是两种方案均存在效率问题，特别是数据量较大的时候。所以最好还是直接返回结果然后在应用程序里重新排序。