使用 MySQL IN() 选择查询并避免在其中排序
mysqlmysqli database
使用 IN() 对特定字段的结果进行排序。为避免这种情况，请对字段使用 ORDER BY 和 FIND_IN_SET()。
为了理解 find_in_set()，让我们创建一个表。创建表的查询如下 −
mysql> create table ProductStock -> ( -> ProductId int, -> ProductName varchar(20), -> ProductQuantity int, -> ProductPrice float -> ); Query OK, 0 rows affected (0.79 sec)
现在，您可以使用 insert 命令在表中插入一些记录。查询如下 −
mysql> insert into ProductStock values(1,'Product-101',10,500.56); Query OK, 1 row affected (0.20 sec) mysql> insert into ProductStock values(25,'Product-111',5,150.00); Query OK, 1 row affected (0.21 sec) mysql> insert into ProductStock values(67,'Product-311',7,1000.50); Query OK, 1 row affected (0.60 sec) mysql> insert into ProductStock values(55,'Product-561',8,900.00); Query OK, 1 row affected (0.24 sec) mysql> insert into ProductStock values(75,'Product-221',15,670.56); Query OK, 1 row affected (0.14 sec)
使用 select 语句显示表中的所有记录。查询如下 −
mysql> select *from ProductStock;
输出
+-----------+-------------+-----------------+--------------+ | ProductId | ProductName | ProductQuantity | ProductPrice | +-----------+-------------+-----------------+--------------+ | 1 | Product-101 | 10 | 500.56 | | 25 | Product-111 | 5 | 150 | | 67 | Product-311 | 7 | 1000.5 | | 55 | Product-561 | 8 | 900 | | 75 | Product-221 | 15 | 670.56 | +-----------+-------------+-----------------+--------------+ 5 rows in set (0.00 sec)
这是使用 find_in_set() 进行的查询 −
mysql> select *from ProductStock -> where ProductId IN(25,55,67,75,1) -> order by find_in_set(ProductId,'25,55,67,75,1');
输出
+-----------+-------------+-----------------+--------------+ | ProductId | ProductName | ProductQuantity | ProductPrice | +-----------+-------------+-----------------+--------------+ | 25 | Product-111 | 5 | 150 | | 55 | Product-561 | 8 | 900 | | 67 | Product-311 | 7 | 1000.5 | | 75 | Product-221 | 15 | 670.56 | | 1 | Product-101 | 10 | 500.56 | +-----------+-------------+-----------------+--------------+ 5 rows in set (0.31 sec)
