MySQL is using Select to discover the live record method, including a comma-delimited string

First, we create a comma-delimited string. CREATE TABLE Test (id int (6) NOT NULL auto_increment,primary KEY (ID), pname varchar (a) not null,pnum varchar (a) NOT null) Then insert a comma-delimited test data insert into Test (pname,pnum) VALUES (' Product 1 ', ' 1,2,4 '), insert into Test (pname,pnum) VALUES (' Product 2 ', ' 2,4,7 ' INSERT into Test (pname,pnum) VALUES (' Product 3 ', ' 3,4 '); INSERT into Test (pname,pnum) VALUES (' Product 4 ', ' 1,7,8,9 '); INSERT into Test (Pname,pnum) VALUES (' Product 5 ', ' 33,4 '); Find records in pnum fields that include 3 or 9 mysql> SELECT * from Test WHERE find_in_set (' 3 ', pnum) or Find_in_set (' 9 ', pnum), +----+-------+---------+| ID | PName |  Pnum |+----+-------+---------+| 3 | Product 3 |  3,4 | | 4 | Product 4 | 1,7,8,9 |+----+-------+---------+2 rows in Set (0.03 sec) using regular mysql> SELECT * from Test WHERE pnum REGEXP ' (3|9) '; +----+ -------+---------+| ID | PName |  Pnum |+----+-------+---------+| 3 | Product 3 |  3,4 | | 4 | Product 4 |  1,7,8,9 | | 5 | Product 5 | 33,4 |+----+-------+---------+3 rows in Set (0.02 sec) This produces more than one record. For example, 33 is also found, but MySQL can also use regular, very interesting find_in_set () function returned to the location, assuming that there is no return 0MYSQL&GT SELECT find_in_set (' e ', ' h,e,l,l,o '); +------------------------------+|                            Find_in_set (' e ', ' h,e,l,l,o ') |+------------------------------+| 2 |+------------------------------+1 row in Set (0.00 sec) can also be used to sort, for example the following;mysql> SELECT * from TEST WHERE ID in (4,2,3); +-- --+-------+---------+| ID | PName |  Pnum |+----+-------+---------+| 2 | Product 2 |  2,4,7 | | 3 | Product 3 |  3,4 | | 4 | Product 4 | 1,7,8,9 |+----+-------+---------+3 rows in Set (0.03 sec) false assumptions are based on ID 4, 2. 3 How about this sort? Mysql> SELECT * from TEST WHERE ID in (4,2,3) ORDER by Find_in_set (ID, ' 4,2,3 '); +----+-------+---------+| ID | PName |  Pnum |+----+-------+---------+| 4 | Product 4 |  1,7,8,9 | | 2 | Product 2 |  2,4,7 | | 3 | Product 3 |  3,4 |+----+-------+---------+3 rows in Set (0.03 sec)

Copyright notice: This article blog original articles, blogs, without consent, may not be reproduced.

MySQL is using Select to discover the live record method, including a comma-delimited string