MySQL FIND_IN_SET()函数笔记

关于 AI

mohのAI正在绞尽脑汁想思路ING···

mohのAI摘要

mohのAI-Lite

碎碎念：项目开发中遇到一个场景——专家评审表(表A)和专家签名表(表B)，表B的aids字段以逗号分隔存储多个表A的id。需要查询表A中未签名的数据，此时就需要用到 FIND_IN_SET() 函数。

MySQL FIND_IN_SET()

函数定义

FIND_IN_SET(str, strList)

参数说明:

• str : 要查找的字符串
• strList: 逗号分隔的字符串的列表

功能说明:

• 功能：在逗号分隔的字符串列表中查找指定值的位置。
• 返回值：
  • 找到时：返回其在列表中的位置索引（从 1 开始）。
  • 未找到时：返回 0
  • 如果任一参数为 NULL，则返回 NULL。

核心特点:

特性	说明
匹配方式	精确匹配整个字符串
分隔符	仅支持逗号（,），不支持其他分隔符
性能	不适合大数据量（通常导致全表扫描）
使用场景	处理历史遗留的逗号分隔字符串字段
索引支持	无法使用普通索引，可考虑函数索引（MySQL 8.0+）

语法与返回值

FIND_IN_SET(str, strlist)

• str: 要查找的字符串
• strlist: 逗号分隔的字符串列表

查找结果	返回值
找到	位置索引（从 1 开始）
未找到	0
任一参数为 NULL	NULL

简单示例

SELECT FIND_IN_SET('b', 'a,b,c,d');   -- 返回 2
SELECT FIND_IN_SET('x', 'a,b,c,d');   -- 返回 0
SELECT FIND_IN_SET('2', '10,2,20');   -- 返回 2
SELECT FIND_IN_SET('2', '10,20,22');  -- 返回 0
SELECT FIND_IN_SET(NULL, 'a,b,c');    -- 返回 NULL
SELECT FIND_IN_SET('b', NULL);        -- 返回 NULL

⚠️ 注意：FIND_IN_SET('2', '10,2,20') 返回 2，而不是 1，因为是精确匹配 “2” 在 “10,2,20” 中的位置。

常见业务场景

场景一：查询未签名的评审记录

-- 写法一：NOT EXISTS（子查询方式）
SELECT t1.*
FROM TRC_PROJECT_ATTEND_REVIEW_RECORD t1
WHERE NOT EXISTS (
    SELECT 1 FROM TRC_PROJECT_ATTEND_REVIEW_SIGN t5
    WHERE FIND_IN_SET(t1.ID, t5.PROJECT_ATTEND_REVIEW_RECORD_ID) > 0
);

-- 写法二：LEFT JOIN（更直观，推荐）
SELECT t1.*
FROM TRC_PROJECT_ATTEND_REVIEW_RECORD t1
LEFT JOIN TRC_PROJECT_ATTEND_REVIEW_SIGN t5
    ON FIND_IN_SET(t1.ID, t5.PROJECT_ATTEND_REVIEW_RECORD_ID) > 0
WHERE t5.ID IS NULL;

💡 两种写法对比：
| 写法 | 优点 | 缺点 |
|———|———|———|
| NOT EXISTS | 语义清晰，碰到第一条匹配就终止 | 子查询可读性稍差 |
| LEFT JOIN | 逻辑直观，一目了然 | 需要额外判断 IS NULL |

场景二：动态构建查询条件

-- 根据用户ID列表查询用户信息
SET @ids = '1,3,5,7';
SELECT * FROM sys_user
WHERE FIND_IN_SET(id, @ids) > 0;

场景三：结合 GROUP_CONCAT 使用

-- 查询每个用户所属的角色名称
SELECT 
    u.id,
    u.username,
    GROUP_CONCAT(r.role_name) AS roles
FROM sys_user u
LEFT JOIN sys_user_role ur ON u.id = ur.user_id
LEFT JOIN sys_role r ON ur.role_id = r.id
GROUP BY u.id;
-- 结果：roles 可能是 "管理员,用户,游客"

-- 再用 FIND_IN_SET 查询拥有特定角色的用户
SELECT * FROM sys_user 
WHERE FIND_IN_SET('管理员', (SELECT GROUP_CONCAT(r.role_name) FROM ...)) > 0;

场景四：多值更新

-- 给多个用户添加角色
UPDATE sys_user_role
SET role_ids = CONCAT(IFNULL(role_ids, ''), ',11,12')
WHERE user_id IN (1, 2, 3);

-- 查询是否包含某角色
SELECT * FROM sys_user_role
WHERE FIND_IN_SET('11', role_ids) > 0;