mysql - 使用 native MySQL 函数查找一个集合中不存在于另一个集合中的元素

Question

我有两个具有逗号分隔 ID 的变量。

SET @Set1 = '1,2,3,4';
SET @Set2 = '3,2,5,6';

我想仅使用 MySQL 函数获取 Set1 中不在 Set2 中的所有元素。在上述情况下，答案是:“1,4”。

请注意，我只想使用 native MySQL 函数。

Answer 1

最简单的方法是标准化您想要查找结果的集合，然后在第二个集合上使用 FIND_IN_SET() ，如下所示

SET @Set1 = '1,2,3,4';
SET @Set2 = '3,2,5,6';

SELECT col 
FROM
(   SELECT
        SUBSTRING_INDEX(SUBSTRING_INDEX(@Set1, ',', n.digit+1), ',', -1) col
    FROM (SELECT @set1) temp
    JOIN(SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) n
        ON LENGTH(REPLACE(@Set1, ',' , '')) <= LENGTH(@Set1)-n.digit
    ORDER BY n.digit
) t 
WHERE NOT FIND_IN_SET(col, @set2);

如果你想捕获尽可能多的逗号分隔数字，那么就这样做

SELECT col 
FROM
(   SELECT
        SUBSTRING_INDEX(SUBSTRING_INDEX(@Set1, ',', n.digit+1), ',', -1) col
    FROM (SELECT @set1) temp
    JOIN
    (   SELECT
            SEQ.SeqValue as digit
        FROM
        (   SELECT (HUNDREDS.SeqValue + TENS.SeqValue + ONES.SeqValue) SeqValue
            FROM(SELECT 0  SeqValue 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) ONES
            CROSS JOIN(SELECT 0 SeqValue UNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 30 UNION ALL SELECT 40 UNION ALL SELECT 50 UNION ALL SELECT 60 UNION ALL SELECT 70 UNION ALL SELECT 80 UNION ALL SELECT 90) TENS
            CROSS JOIN(SELECT 0 SeqValue UNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300 UNION ALL SELECT 400 UNION ALL SELECT 500 UNION ALL SELECT 600 UNION ALL SELECT 700 UNION ALL SELECT 800 UNION ALL SELECT 900) HUNDREDS
        ) SEQ
    ) n
        ON LENGTH(REPLACE(@Set1, ',' , '')) <= LENGTH(@Set1)-n.digit
    ORDER BY n.digit
) t 
WHERE NOT FIND_IN_SET(col, @set2);

它将在单个逗号分隔列表中返回最多 1000 个项目