MYSQL uses select to query the notes that contain strings separated by commas (,) in a field.

In MYSQL, we use the select statement to query records that contain comma-separated strings in a field. First, we create a string with comma-separated strings. CREATETABLEtest (idint (6) NOTNULLAUTO_INCREMENT, PRIMARYKEY (id), pnameVARCHAR (20) NOTNULL, pnumVARCHAR (50) NOTNULL); then insert

In MYSQL, we use the select statement to query records that contain comma-separated strings in a field. First, we create a string with comma-separated strings. Create table test (id int (6) not null AUTO_INCREMENT, primary key (id), pname VARCHAR (20) not null, pnum VARCHAR (50) not null); then insert

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 test data separated by commas (,).
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 ');


Search for records whose pnum field contains 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
+ ---- + ------- + --------- +
2 rows in set (0.03 sec)


Use Regular Expressions
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 will generate multiple records, for example, 33 is also found, but MYSQL can also use regular expressions, which is quite interesting.


The position returned by the find_in_set () function. If it does not exist, 0 is returned.
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)

It 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 | 1, 7, 8, 9 |
+ ---- + ------- + --------- +
3 rows in set (0.03 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 | 1, 7, 8, 9 |
|? 2 | product 2 | 2, 4, 7 ?? |
|? 3 | product 3 | 3, 4 ???? |
+ ---- + ------- + --------- +
3 rows in set (0.03 sec )?

Http://www.itokit.com/2012/0606/74328.html