如何在 MySql 中用 WHERE `id` IN (…) 指定精确顺序?
mysqlmysqli database
要用 where id IN 指定精确顺序，您需要使用 find_in_set() 函数。
语法如下
SELECT *FROM yourTableName WHERE yourColumnName IN (yourValue1,yourValue2,yourValue3,....N) ORDER BY FIND_IN_SET(yourColumnName , ‘yourValue1,yourValue2,yourValue3,....N’');
Let us first create a table
mysql> create table FindInSetDemo - > ( - > Id int, - > Name varchar(20), - > Age int - > ); Query OK, 0 rows affected (0.54 sec)
使用 insert 命令在表中插入一些记录。
查询如下
mysql> insert into FindInSetDemo values(10,'John',23); Query OK, 1 row affected (0.20 sec) mysql> insert into FindInSetDemo values(1,'Carol',21); Query OK, 1 row affected (0.17 sec) mysql> insert into FindInSetDemo values(4,'Bob',25); Query OK, 1 row affected (0.19 sec) mysql> insert into FindInSetDemo values(6,'Sam',26); Query OK, 1 row affected (0.15 sec) mysql> insert into FindInSetDemo values(7,'Maxwell',29); Query OK, 1 row affected (0.18 sec) mysql> insert into FindInSetDemo values(8,'Mike',22); Query OK, 1 row affected (0.13 sec) mysql> insert into FindInSetDemo values(2,'David',27); Query OK, 1 row affected (0.20 sec) mysql> insert into FindInSetDemo values(3,'James',20); Query OK, 1 row affected (0.20 sec)
使用 select 语句显示表中的所有记录。
查询如下
mysql> select *from FindInSetDemo;
以下是输出 −
+------+---------+------+ | Id | Name | Age | +------+---------+------+ | 10 | John | 23 | | 1 | Carol | 21 | | 4 | Bob | 25 | | 6 | Sam | 26 | | 7 | Maxwell | 29 | | 8 | Mike | 22 | | 2 | David | 27 | | 3 | James | 20 | +------+---------+------+ 8 rows in set (0.00 sec)
这是使用 where id IN() 指定精确顺序的查询
mysql> select *from FindInSetDemo - > where Id IN (1,4,6,7) - > order by FIND_IN_SET(Id, '1,4,6,7');
以下是输出 −
+------+---------+------+ | Id | Name | Age | +------+---------+------+ | 1 | Carol | 21 | | 4 | Bob | 25 | | 6 | Sam | 26 | | 7 | Maxwell | 29 | +------+---------+------+ 4 rows in set (0.00 sec)
