如何在 MySql 中使用 WHERE `id` IN (...) 指定确切的顺序？
要使用whereidIN指定确切顺序，您需要使用find_in_set()函数。
语法如下
SELECT *FROM yourTableName WHERE yourColumnName IN (yourValue1,yourValue2,yourValue3,....N) ORDER BY FIND_IN_SET(yourColumnName , ‘yourValue1,yourValue2,yourValue3,....N’');
让我们先创建一个表
mysql> create table FindInSetDemo - > ( - > Id int, - > Name varchar(20), - > Age int - > );
使用插入命令在表中插入一些记录。
查询如下
mysql> insert into FindInSetDemo values(10,'John',23); mysql> insert into FindInSetDemo values(1,'Carol',21); mysql> insert into FindInSetDemo values(4,'Bob',25); mysql> insert into FindInSetDemo values(6,'Sam',26); mysql> insert into FindInSetDemo values(7,'Maxwell',29); mysql> insert into FindInSetDemo values(8,'Mike',22); mysql> insert into FindInSetDemo values(2,'David',27); mysql> insert into FindInSetDemo values(3,'James',20);
使用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)
这是用whereid指定确切顺序的查询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)