使用 MySQL IN() 选择查询并避免在其中排序
使用IN()对特定字段的结果进行排序。为避免这种情况，请FIND_IN_SET()对字段使用ORDERBY和。
为了理解find_in_set()，让我们创建一个表。创建表的查询如下-
mysql> create table ProductStock -> ( -> ProductId int, -> ProductName varchar(20), -> ProductQuantity int, -> ProductPrice float -> );
现在您可以使用插入命令在表中插入一些记录。查询如下-
mysql> insert into ProductStock values(1,'Product-101',10,500.56); mysql> insert into ProductStock values(25,'Product-111',5,150.00); mysql> insert into ProductStock values(67,'Product-311',7,1000.50); mysql> insert into ProductStock values(55,'Product-561',8,900.00); mysql> insert into ProductStock values(75,'Product-221',15,670.56);
使用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)