MySQL查询：对比逗号分隔列userid与update_userid获取不匹配去重值

嘿，我来帮你搞定这个MySQL查询需求！要对比逗号分隔的userid和update_userid字段，找出两边不匹配且去重的值，咱们得先把这些逗号串拆成单独的行——毕竟直接对比整串字符串根本没法精准匹配单个ID对吧？

核心思路 #

1. 拆分逗号分隔字段：用递归CTE（公共表表达式）把每个字段里的ID拆成单独的记录；
2. 合并数据集：把两个字段的拆分结果合在一起，标记每个值的来源；
3. 筛选不匹配值：通过分组统计，找出只在其中一个字段出现的值，这些就是两边不匹配的目标值。

完整SQL代码 #

WITH split_userids AS (
    -- 拆分userid字段，处理逗号分隔的ID
    SELECT 
        id,
        department,
        TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(t.userid, ',', n.n), ',', -1)) AS user_val,
        'userid' AS source
    FROM your_table t
    CROSS JOIN (
        -- 生成足够多的数字行，覆盖最大的逗号分隔数量（这里支持最多100个，可按需调整）
        SELECT 1 + units.i + tens.i * 10 AS n
        FROM (SELECT 0 i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) units
        CROSS JOIN (SELECT 0 i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) tens
    ) n
    WHERE n.n <= 1 + LENGTH(t.userid) - LENGTH(REPLACE(t.userid, ',', ''))
      AND TRIM(t.userid) != ''
      AND t.userid IS NOT NULL
),
split_update_userids AS (
    -- 拆分update_userid字段，逻辑和上面一致
    SELECT 
        id,
        department,
        TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(t.update_userid, ',', n.n), ',', -1)) AS user_val,
        'update_userid' AS source
    FROM your_table t
    CROSS JOIN (
        SELECT 1 + units.i + tens.i * 10 AS n
        FROM (SELECT 0 i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) units
        CROSS JOIN (SELECT 0 i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) tens
    ) n
    WHERE n.n <= 1 + LENGTH(t.update_userid) - LENGTH(REPLACE(t.update_userid, ',', ''))
      AND TRIM(t.update_userid) != ''
      AND t.update_userid IS NOT NULL
)
-- 筛选只在其中一个字段出现的ID，自动去重
SELECT DISTINCT user_val
FROM (
    SELECT user_val, source FROM split_userids
    UNION ALL
    SELECT user_val, source FROM split_update_userids
) combined
GROUP BY user_val
HAVING COUNT(DISTINCT source) = 1;

代码说明 #

• 拆分逻辑：通过数字表生成行号，配合SUBSTRING_INDEX把每个逗号分隔的ID拆成单独行，TRIM用来去除ID前后可能的空格，避免因空格导致的误判；
• 空值处理：WHERE条件里排除了空字符串和NULL值，避免无效数据干扰结果；
• 筛选逻辑：合并两个拆分结果后，按ID分组统计来源数量，COUNT(DISTINCT source) = 1意味着这个ID只在userid或update_userid中出现，也就是两边不匹配的目标值。

如果你的表中逗号分隔的ID数量超过100个，只需扩展数字表的生成逻辑（比如增加百位的数字集合）即可支持更多ID的拆分。

内容的提问来源于stack exchange，提问作者Anonymous