当前位置：首页 > news >正文

mysql中find_in_set()函数的使用, ancestors字段,树形查询

news 2025/9/5 11:38:52

1. 函数定义与作用

FIND_IN_SET(str, strlist) 函数用于在一个由逗号分隔的字符串列表（strlist） 中查找某个特定字符串（str） 的位置。

返回值：如果找到字符串，则返回其所在的位置（从 1 开始计数）；如果未找到，则返回 0；如果任一参数为 NULL，则返回 NULL。
核心特点：它专门用于处理以逗号 , 分隔的字符串，而不是任意分隔符。

2. 语法

FIND_IN_SET(needle, haystack)

needle：要查找的字符串。
haystack：由逗号分隔的字符串列表。

3. 使用示例

示例 1：基本查找

SELECT FIND_IN_SET('b', 'a,b,c,d'); -- 返回 2
SELECT FIND_IN_SET('x', 'a,b,c,d'); -- 返回 0
SELECT FIND_IN_SET('', 'a,b,c,d');  -- 返回 0 (空字符串找不到)
SELECT FIND_IN_SET('b', '');        -- 返回 0
SELECT FIND_IN_SET(NULL, 'a,b,c');  -- 返回 NULL
SELECT FIND_IN_SET('a', NULL);      -- 返回 NULL

4. 示例

正确示范（使用 FIND_IN_SET()）：
FIND_IN_SET() 可以精确匹配列表中的每一项。

SELECT * FROM users WHERE FIND_IN_SET('music', hobbies) > 0;

结果：

id	name	hobbies
1	Alice	reading,music,hiking
2	Bob	music,game

注意：> 0 可以省略，因为在 MySQL 中非零值被视为 TRUE，零被视为 FALSE。但为了代码清晰，建议保留。

-- 这样写也是有效的
SELECT * FROM users WHERE FIND_IN_SET('music', hobbies);

5. 树形结构妙用

部门表, 添加修改维护 ancestors字段, 记录祖先节点ID. 查询子部门可以通过下面的sql语句

select * from sys_dept where find_in_set(#{deptId}, ancestors)

6. 注意事项与局限性

性能问题：FIND_IN_SET() 函数无法使用索引。因为它需要遍历整个字符串列表并进行拆分匹配。在数据量大的表上，用 WHERE FIND_IN_SET(...) > 0 作为查询条件会导致全表扫描，性能非常差。
设计缺陷：像上面 hobbies 这样用一个字段存储多个值（逗号分隔），违反了数据库设计的第一范式（1NF）。这会导致数据冗余、更新困难、查询复杂且低效。
精确匹配：它是完全匹配，区分大小写（取决于您的字符集和校对规则）。FIND_IN_SET(‘B’, ‘a,b,c’) 和 FIND_IN_SET(‘b’, ‘a,B,c’) 的结果可能不同。
仅支持逗号分隔：它只能处理逗号 , 分隔的列表。如果您的列表是用分号 ; 或空格等其他符号分隔的，这个函数将无法工作。