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

秒速五厘米 2022-08-20 10:25 137阅读 0赞

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