mysql in() find_in_set() 函数

in常用于where表达式中，其作用是查询某个范围内的数据，not in与in作用相反。

select * from where field in (value1,value2,value3,…)
select * from where field not in (value1,value2,value3,…)

find_in_set(arg1,arg2)
arg1 要查询字符串或表字段，arg2 可为逗号分隔的字符串或表字段。

---

常规演示

SELECT  1 in (1,2,3); //1
SELECT  4 in (1,2,3); //0
SELECT FIND_IN_SET(2,'1,2,3')   //2  从1开始
SELECT FIND_IN_SET(4,null) //null  
SELECT FIND_IN_SET(4,'1,2,3')   //0
SELECT FIND_IN_SET(4,'') //0

---

建表查询演示

CREATE TABLE IF NOT EXISTS tb_student(
	id INT NOT NULL auto_increment ,
	name VARCHAR(255) NOT NULL,
	hobby VARCHAR(255) NOT NULL,
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO `tb_student` (`name`,`hobby`) VALUES('张三','篮球,羽毛球');
INSERT INTO `tb_student` (`name`,`hobby`) VALUES('李四','篮球,足球');
INSERT INTO `tb_student` (`name`,`hobby`) VALUES('王五','足球,乒乓球');
INSERT INTO `tb_student` (`name`,`hobby`) VALUES('Lisa','篮球,羽毛球,游泳');
INSERT INTO `tb_student` (`name`,`hobby`) VALUES('Tom','网球');
	

mysql> SELECT * FROM tb_student WHERE id in(1,2);
+----+--------+----------------------+
| id | name   | hobby                |
+----+--------+----------------------+
|  1 | 张三   | 篮球,羽毛球          |
|  2 | 李四   | 篮球,足球,网球       |
+----+--------+----------------------+
2 rows in set (0.01 sec)

mysql> SELECT * FROM tb_student WHERE id in('1','2');
+----+--------+----------------------+
| id | name   | hobby                |
+----+--------+----------------------+
|  1 | 张三   | 篮球,羽毛球          |
|  2 | 李四   | 篮球,足球,网球       |
+----+--------+----------------------+
2 rows in set (0.00 sec)

// 当in 后跟逗号分隔字符串时 查询结果不满足要求
mysql> SELECT * FROM tb_student WHERE id in('1,2');
+----+--------+------------------+
| id | name   | hobby            |
+----+--------+------------------+
|  1 | 张三   | 篮球,羽毛球      |
+----+--------+------------------+
1 row in set, 1 warning (0.00 sec)

//find_in_set可以直接使用逗号分隔字符串参数，l此时效果同 id in(1,2)
mysql> SELECT * FROM tb_student WHERE FIND_IN_SET(id,'1,2');
+----+------+-------------------------+
| id | name | hobby                   |
+----+------+-------------------------+
|  4 | Lisa | 篮球,羽毛球,游泳        |
|  5 | Tom  | 网球                    |
+----+------+-------------------------+
2 rows in set (0.00 sec)

//find_in_set 不局限于int
mysql> SELECT * FROM tb_student WHERE FIND_IN_SET(name,'Lisa,tom');
+----+------+-------------------------+
| id | name | hobby                   |
+----+------+-------------------------+
|  4 | Lisa | 篮球,羽毛球,游泳        |
|  5 | Tom  | 网球                    |
+----+------+-------------------------+
2 rows in set (0.00 sec)


//查询hobby字段中逗号分隔符间有网球的记录
mysql> SELECT * FROM tb_student WHERE FIND_IN_SET('网球',hobby);
+----+--------+----------------------+
| id | name   | hobby                |
+----+--------+----------------------+
|  2 | 李四   | 篮球,足球,网球       |
|  5 | Tom    | 网球                 |
+----+--------+----------------------+
2 rows in set (0.00 sec)