How MySQL queries a comma-delimited string in a field

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 the test data with a comma-delimited
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 with 3 or 9 in the Pnum field
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 the 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 results in multiple records, such as 33 being found.

In a different way

1. Mysql> SELECT * from test WHERE CONCAT (', ', ' Pnum,', ') REGEXP ' [^0-9]+[3|9][^0-9]+ ';

+----+-------+---------+
| ID | PName | Pnum |
+----+-------+---------+
| 3 | Product 3 | 3,4 |
| 4 | Product 4 | 1,7,8,9 |
+----+-------+---------+
2 rows in Set (0.01 sec)

3. Select * from oa_student_archives where CONCAT (', ', "pe_projects, ', ') like '%,11,% ';

This way is also possible.

Attention:

Red mysql> SELECT * from test WHERE CONCAT (', ', Pnum,', ') REGEXP ' [^0-9]+[3|9][^0-9]+ ‘;

This, at that time less than 10 of the data search is OK, but if it is greater than 10, it can not be used, the reason I am not quite clear, the great God can explain it?

Summarized as follows:

1. Select * from oa_student_archives where CONCAT (', ', "pe_projects, ', ') like '%,11,% ';

2. Select * from oa_student_archives where find_in_set(' n ', pe_projects);

All of the above two can be used.

How MySQL queries a comma-delimited string in a field