First we create a comma-delimited string.CREATE TABLE Test (id int (6) Not NULL auto_increment,primary KEY (ID), pname varchar (no null,pnum varchar) not N ULL);
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 will produce multiple records, such as 33 is also found, but MySQL can also use regular, very interesting
The location where the Find_in_set () function returns and returns 0 if it does not exist
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 to sort, as follows;
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 (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)
DROP TABLE test_product; CREATE TABLE test_product (id int (6) Not NULL auto_increment,primary KEY (ID), pname varchar (a) not null,pnum varchar (N) OT NULL); insert into test_product (pname,pnum) VALUES (' Product1 ', ' 1,2,4 '); insert into test_product (pname,pnum) VALUES ( ' Product2 ', ' 2,4,7 '), insert into Test_product (pname,pnum) VALUES (' product3 ', ' 3,4 '); insert into test_product (PName, Pnum) VALUES (' product14 ', ' 1,7,8,9 '); insert into test_product (pname,pnum) VALUES (' product15 ', ' 33,4 '); INSERT INTO Test _product (pname,pnum) VALUES (' Product 2342 ', ' 33,4 '); SELECT * from Test_product WHERE find_in_set (' ', pnum) OR find_in_set (' 9 ', pnum);
Using select in MySQL to query a field for a record method that contains a comma-delimited string