MySQL中picklist类型多值字段的跨列匹配查询实现问题

你之前使用IN子查询的写法只能匹配整串完全相等的记录，无法识别分号分隔的单个独立值，因此只会返回部分正确结果。

匹配规则参考示例 #

正确实现方案 #

方法1：正则匹配（兼容所有MySQL版本，写法简单） #

通过构造正则表达式判断任意一个值是否完整存在于另一列的分号分隔值序列中，双向任意一个匹配就判定为命中：

SELECT 
    col1,
    col2,
    CASE 
        WHEN col2 REGEXP CONCAT('(^|;)', REPLACE(col1, ';', '|'), '(;|$)') 
            OR col1 REGEXP CONCAT('(^|;)', REPLACE(col2, ';', '|'), '(;|$)')
        THEN 'Match'
        ELSE 'Un-match'
    END AS `Match column`
FROM 你的实际表名;

逻辑说明：

• 用REPLACE把分号替换为正则的或符号|，把MA;NY这类多值转为MA|NY
• 前后拼接(^|;)和(;|$)是为了避免部分匹配，比如防止NY错误匹配到NYC这类前缀相似的值，保证匹配的是完整的单个取值
• 两个方向的正则判断同时覆盖「col1任意值在col2」和「col2任意值在col1」两个规则

方法2：JSON_TABLE拆分（MySQL 8.0+支持，性能更好适合大数据量） #

如果你的MySQL版本在8.0及以上，可以先把多值列拆分为单行再做等值匹配，查询性能更高：

WITH split_col1 AS (
    SELECT 
        t.*,
        j1.val AS val1
    FROM 你的实际表名 t
    JOIN JSON_TABLE(
        CONCAT('["', REPLACE(t.col1, ';', '","'), '"]'),
        '$[*]' COLUMNS (val VARCHAR(255) PATH '$')
    ) j1
),
split_col2 AS (
    SELECT 
        t.*,
        j2.val AS val2
    FROM 你的实际表名 t
    JOIN JSON_TABLE(
        CONCAT('["', REPLACE(t.col2, ';', '","'), '"]'),
        '$[*]' COLUMNS (val VARCHAR(255) PATH '$')
    ) j2
)
SELECT 
    DISTINCT s1.col1,
    s1.col2,
    IF(s2.val2 IS NOT NULL, 'Match', 'Un-match') AS `Match column`
FROM split_col1 s1
LEFT JOIN split_col2 s2 
    ON s1.col1 = s2.col1 
    AND s1.col2 = s2.col2 
    AND s1.val1 = s2.val2;

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