MySql 之 FIND_IN_SET 和IN

CREATE TABLE `test` (

  `id` int(8) NOT NULL auto_increment,

  `name` varchar(255) NOT NULL,

  `list` varchar(255) NOT NULL,

  PRIMARY KEY  (`id`)

)

INSERT INTO `test` VALUES (1, 'name', 'daodao,xiaohu,xiaoqin');

INSERT INTO `test` VALUES (2, 'name2', 'xiaohu,daodao,xiaoqin');

INSERT INTO `test` VALUES (3, 'name3', 'xiaoqin,daodao,xiaohu');

mysql> select id, list, name from table where 'daodao' IN (list);

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'table
where 'daodao' IN (list)' at line 1

 

mysql> select * from test where find_in_set('daodao',list);
+----+-------+-----------------------+
| id | name | list |
+----+-------+-----------------------+
| 1 | name | daodao,xiaohu,xiaoqin |
| 2 | name2 | xiaohu,daodao,xiaoqin |
| 3 | name3 | xiaoqin,daodao,xiaohu |
+----+-------+-----------------------+
3 rows in set (0.00 sec)