select * from a where instr(LPAD(RPAD(Group_id,length(Group_id)+1,','),length(Group_id)+2,','),',3,')<>0 and instr(LPAD(RPAD(Group_id,length(Group_id)+1,','),length(Group_id)+2,','),',1,')<>0
mysql> use c1 Database changed mysql> select * from a; +----+----------------+ | id | Talent | +----+----------------+ | 1 | 1,2,3,4,5 | | 2 | 1,3 | | 3 | 1,123,132,3,34 | | 4 | 1,12,13 | +----+----------------+ 4 rows in set (0.02 sec)mysql> SELECT * FROM a where instr(LPAD(RPAD(talent,length(talent)+1,','),length (talent)+2,','),',3,')<>0 -> and instr(LPAD(RPAD(talent,length(talent)+1,','),length(talent)+2,','),', 1,')<>0; +----+----------------+ | id | Talent | +----+----------------+ | 1 | 1,2,3,4,5 | | 2 | 1,3 | | 3 | 1,123,132,3,34 | +----+----------------+ 3 rows in set (0.00 sec)mysql>
http://blog.csdn.net/ACMAIN_CHM/archive/2009/05/01/4141864.aspx
and instr(LPAD(RPAD(Group_id,length(Group_id)+1,','),length(Group_id)+2,','),',1,')<>0
Database changed
mysql> select * from a;
+----+----------------+
| id | Talent |
+----+----------------+
| 1 | 1,2,3,4,5 |
| 2 | 1,3 |
| 3 | 1,123,132,3,34 |
| 4 | 1,12,13 |
+----+----------------+
4 rows in set (0.02 sec)mysql> SELECT * FROM a where instr(LPAD(RPAD(talent,length(talent)+1,','),length
(talent)+2,','),',3,')<>0
-> and instr(LPAD(RPAD(talent,length(talent)+1,','),length(talent)+2,','),',
1,')<>0;
+----+----------------+
| id | Talent |
+----+----------------+
| 1 | 1,2,3,4,5 |
| 2 | 1,3 |
| 3 | 1,123,132,3,34 |
+----+----------------+
3 rows in set (0.00 sec)mysql>