MySQL FIND_IN_SET 查逗号分隔特定值函数

MySQL的FIND_IN_SET函数是一个非常实用的函数，它可以在一个逗号分隔的字符串列表中查找一个特定的值。这个函数在处理类似CSV数据或者需要模糊匹配的场景下特别有用。本文将详细介绍FIND_IN_SET函数的语法、使用案例以及需要注意的事项。

语法

FIND_IN_SET函数的语法如下：

FIND_IN_SET(search_value, string_list)

其中，search_value是要查找的值，string_list是逗号分隔的字符串列表。

假如字符串str在由N个子链组成的字符串列表strlist 中，则返回值的范围在 1 到 N 之间。 一个字符串列表就是一个由一些被 ',' 符号分开的子链组成的字符串。如果第一个参数是一个常数字符串，而第二个是type SET列，则FIND_IN_SET() 函数被优化，使用比特计算。 如果str不在strlist 或strlist 为空字符串，则返回值为 0 。如任意一个参数为NULL，则返回值为 NULL。这个函数在第一个参数包含一个逗号( , )时将无法正常运行。

示例：

SELECT FIND_IN_SET('b', 'a,b,c,d');
// 结果：2
// 因为 b 在strlist集合中2的位置, a是位置1
 
select FIND_IN_SET('1', '1');
// 结果：1 
// 这时候的strlist集合有点特殊，只有一个字符串
 
select FIND_IN_SET('2', '1,2'); 
// 结果：2
 
select FIND_IN_SET('6', '1'); 
// 结果：0 strlist中不存在str，所以返回0。

find_in_set案例

让我们来看一个具体的案例。

CREATE TABLE `test` (
  `ID` int(11) NOT NULL,
  `LIST` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO `test` VALUES ('1', 'AA,BB,CD');
INSERT INTO `test` VALUES ('2', 'AA,BB');
INSERT INTO `test` VALUES ('3', 'AA');

原始数据

mysql> SELECT * FROM test;
+----+----------+
| ID | LIST     |
+----+----------+
|  1 | AA,BB,CD |
|  2 | AA,BB    |
|  3 | AA       |
+----+----------+
3 rows in set (0.00 sec)

用test表，找list列中的字符分隔值有“BB”的。

mysql> SELECT * FROM test WHERE FIND_IN_SET('BB', list) ;
+----+----------+
| ID | LIST     |
+----+----------+
|  1 | AA,BB,CD |
|  2 | AA,BB    |
+----+----------+
2 rows in set (0.00 sec)

find_in_set() 和 in 的区别

• in后面只能跟常量（通过范围查找数据）in是完全匹配。

•  find_in_set()函数可以使用常量或字段，find_in_set()函数是精确匹配，字段值以英文”,”分隔。（通过字符查询数据）。

mysql> SELECT * FROM test WHERE id IN (2,3);
+----+-------+
| ID | LIST  |
+----+-------+
|  2 | AA,BB |
|  3 | AA    |
+----+-------+
2 rows in set (0.05 sec)

-- IN查询字段条件，查询id是2或者3的数据

find_in_set与like的区别

• like是广泛的模糊匹配，字符串中没有分隔符。

• find_in_set是精确匹配，字段值以英文“，”分隔，find_in_set查询的结果要小于like查询的结果。

mysql> select * from test where find_in_set('D',list);
Empty set (0.00 sec)

mysql> select * from test where list like '%D%';
+----+----------+
| ID | LIST     |
+----+----------+
|  1 | AA,BB,CD |
+----+----------+
1 row in set (0.00 sec)

这时，select * from test where find_in_set('D',list);这样是查不到的，返回值为null，因为follow_id中没有“D”这个值，它不同于like模糊查询，它是以“，”来分割。如果使用like查询，查询结果为id=1的一条记录。

应用场景

1、文章表type字段查询

文章表里面有个type字段，它存储的是文章类型，有 1头条、2推荐、3热点、4图文等等 。现在有篇文章他既是头条，又是热点，还是图文，type中以 1,3,4 的格式存储。那我们如何用sql查找所有type中有4的图文类型的文章呢？

select * from article where FIND_IN_SET('4',type)

 2、部门树查询，匹配当前节点及所有子节点

2.1 数据表字段说明

 2.2 匹配部门id或父id为100的数据

SELECT dept_id FROM sys_dept WHERE dept_id = 100 or FIND_IN_SET( 100 , ancestors )

注意事项

虽然FIND_IN_SET函数非常方便，但是在使用时需要注意以下几点：

• 性能问题：由于FIND_IN_SET函数需要在字符串列表中进行搜索，因此对于大数据量的表来说，查询可能会非常慢。如果可能的话，尽量避免在大的CSV字符串中进行搜索。

• 数据一致性：由于FIND_IN_SET函数是直接操作字符串，因此在使用过程中需要特别注意数据的一致性。例如，如果在某次更新中误将'running,swimming'改为了'running'，那么这个值就永远丢失了，无法恢复。

• 类型限制：FIND_IN_SET函数只接受字符串类型的输入。如果你尝试在非字符串类型的列中使用这个函数，将会导致错误。

• 大小写敏感性：FIND_IN_SET函数是大小写敏感的。这意味着如果你在查找的字符串中使用了大写字母，那么在CSV字符串列表中也需要使用相应的大写字母才能匹配到。

• 空值问题：如果interests列中存在空值，那么在使用FIND_IN_SET函数时需要特别小心。因为如果查找的字符串不在空字符串中，那么返回的结果将是0，这可能会误导用户认为该行记录不满足条件。因此，在使用这个函数之前，最好先对空值进行处理。