How to Use select to query records that contain strings separated by commas in a field in MYSQL
First, create a string separated by commas. Create table test (id int (6) not null AUTO_INCREMENT, primary key (id), pname VARCHAR (20) not null, pnum VARCHAR (50) not null ); insert into test (pname, pnum) VALUES ('product 1', '1, 2'); 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 the pnum Field Mysql> SELECT * FROM test WHERE find_in_set ('3', pnum) OR find_in_set ('9', pnum), which contains 3 OR 9 records ); + ---- + ------- + --------- + | id | pname | pnum | + ---- + ------- + --------- + | 3 | product 3 | 3,4 | 4 | product 4 |, 8, 9 | + ---- + ------- + --------- + 2 rows in set (0.03 sec) use regular mysql> SELECT * FROM test WHERE pnum REGEXP '(3 | 9 )'; + ---- + ------- + --------- + | id | pname | pnum | + ---- + ------- + --------- + | 3 | product 3 | 3, 4 | 4 | product 4 | 1, 7, 0.02 | 5 | product 5 | 33,4 | + ---- + ------- + --------- + 3 rows in set (sec) Multiple records are generated, for example, 33 is also found, but MYSQL can also use regular expressions. It is interesting to see where the find_in_set () function returns, if it does not exist, return 0 mysql> 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 for sorting, as shown below; 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 | 0.03, | + ---- + ------- + --------- + 3 rows in set (sec) what if you want to sort by ID 4, 2, 3? Mysql> SELECT * from test where id in (, 3) order by find_in_set (id, '4 '); + ---- + ------- + --------- + | id | pname | pnum | + ---- + ------- + --------- + | 4 | product 4 |, | 2 | product 2 | 2, 4, 7 | 3 | product 3 | 3, 4 | + ---- + ------- + --------- + 3 rows in set (0.03 sec)