?
é¦å
æ们建ç«ä¸å¼ 带æéå·åéçå符串ã
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('产å1','1,2,4');
INSERT INTO test(pname,pnum) VALUES('产å2','2,4,7');
INSERT INTO test(pname,pnum) VALUES('产å3','3,4');
INSERT INTO test(pname,pnum) VALUES('产å4','1,7,8,9');
INSERT INTO test(pname,pnum) VALUES('产å5','33,4');
æ¥æ¾pnumå段ä¸å
å«3æè
9çè®°å½
mysql> SELECT * FROM test WHERE find_in_set('3',pnum) OR find_in_set('9',pnum);
+----+-------+---------+
| id | pname | pnum |
+----+-------+---------+
| 3 | 产å3 | 3,4 |
| 4 | 产å4 | 1,7,8,9 |
+----+-------+---------+
2 rows in set (0.03 sec)
使ç¨æ£å
mysql> SELECT * FROM test WHERE pnum REGEXP '(3|9)';
+----+-------+---------+
| id | pname | pnum |
+----+-------+---------+
| 3 | 产å3 | 3,4 |
| 4 | 产å4 | 1,7,8,9 |
| 5 | 产å5 | 33,4 |
+----+-------+---------+
3 rows in set (0.02 sec)
è¿æ ·ä¼äº§çå¤æ¡è®°å½ï¼æ¯å¦33ä¹è¢«æ¥æ¾åºæ¥äºï¼ä¸è¿MYSQLè¿å¯ä»¥ä½¿ç¨æ£åï¼æºæææç
find_in_set()å½æ°è¿åçæå¨çä½ç½®ï¼å¦æä¸åå¨å°±è¿å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)
è¿å¯ä»¥ç¨æ¥æåºï¼å¦ä¸;
mysql> SELECT * FROM TEST WHERE id in(4,2,3);
+----+-------+---------+
| id | pname | pnum |
+----+-------+---------+
| 2 | 产å2 | 2,4,7 |
| 3 | 产å3 | 3,4 |
| 4 | 产å4 | 1,7,8,9 |
+----+-------+---------+
3 rows in set (0.03 sec)
å¦ææ³è¦æç
§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 | 产å4 | 1,7,8,9 |
| 2 | 产å2 | 2,4,7 |
| 3 | 产å3 | 3,4 |
+----+-------+---------+
3 rows in set (0.03 sec)
温馨提示:答案为网友推荐,仅供参考