MySQL技术问询：筛选含全部指定标签且无排除标签的ItemID

需求回顾 #

• 单个条目可关联30+个标签
• 输入为两个逗号分隔字符串：Included（示例：'Crimson,Violet'）、Excluded（示例：'Khaki,Teal'）
• 筛选目标：包含Included中全部标签，且完全不包含Excluded中任何标签的ItemID
• 条目可能存在重复标签（标签带时间属性）
• 涉及三张表：主表（含ItemID等字段）、关联表（ItemID与TagID关联）、标签表（TagID与TagName对应）

原代码存在的问题 #

1. 变量语法错误：MySQL中变量赋值需使用SET @var = value;，原代码直接赋值的写法不符合语法规范
2. 变量未初始化：@returnedValues变量未赋值，导致标签数量计算逻辑完全失效
3. 排除逻辑漏洞：仅通过WHERE NOT Find_in_set(...)过滤当前关联的标签，无法保证该ItemID未关联其他属于Excluded列表的标签
4. 空格匹配问题：Find_in_set会将逗号后带空格的内容视为独立标签（如'Crimson, Violet'中的' Violet'），导致匹配失败

正确实现方案 #

方案一：子查询排除禁止标签（逻辑清晰） #

先通过子查询排除所有关联了禁止标签的ItemID，再筛选满足全部包含标签的条目：

-- 定义输入参数，先去除标签间的空格避免匹配错误
SET @myIncludedValues = REPLACE('Crimson, Violet', ' ', '');
SET @myExcludedValues = REPLACE('Khaki', ' ', '');

-- 计算需要匹配的包含标签总数
SET @includedTagCount = IF(CHAR_LENGTH(@myIncludedValues) > 0, 
                           CHAR_LENGTH(@myIncludedValues) - CHAR_LENGTH(REPLACE(@myIncludedValues, ',', '')) + 1, 
                           0);

SELECT DISTINCT lt.ItemID
FROM linking_table lt
JOIN tag_table tt ON lt.TagID = tt.TagID
-- 仅保留关联到包含标签的记录
WHERE FIND_IN_SET(tt.TagName, @myIncludedValues)
-- 排除所有关联过禁止标签的ItemID
AND lt.ItemID NOT IN (
    SELECT DISTINCT lt_excl.ItemID
    FROM linking_table lt_excl
    JOIN tag_table tt_excl ON lt_excl.TagID = tt_excl.TagID
    WHERE FIND_IN_SET(tt_excl.TagName, @myExcludedValues)
)
GROUP BY lt.ItemID
-- 确保ItemID关联的去重后标签数量等于需要匹配的包含标签数
HAVING COUNT(DISTINCT tt.TagName) = @includedTagCount
ORDER BY lt.ItemID;

方案二：LEFT JOIN排除禁止标签（性能更优） #

通过LEFT JOIN判断无匹配禁止标签的方式，避免子查询可能带来的性能瓶颈，适合大数据量场景：

SET @myIncludedValues = REPLACE('Crimson, Violet', ' ', '');
SET @myExcludedValues = REPLACE('Khaki', ' ', '');
SET @includedTagCount = IF(CHAR_LENGTH(@myIncludedValues) > 0, 
                           CHAR_LENGTH(@myIncludedValues) - CHAR_LENGTH(REPLACE(@myIncludedValues, ',', '')) + 1, 
                           0);

SELECT lt.ItemID
FROM linking_table lt
JOIN tag_table tt ON lt.TagID = tt.TagID
-- 左连接禁止标签的关联记录，仅匹配属于Excluded列表的标签
LEFT JOIN linking_table lt_excl ON lt.ItemID = lt_excl.ItemID
LEFT JOIN tag_table tt_excl ON lt_excl.TagID = tt_excl.TagID 
    AND FIND_IN_SET(tt_excl.TagName, @myExcludedValues)
WHERE FIND_IN_SET(tt.TagName, @myIncludedValues)
-- 确保该ItemID未关联任何禁止标签
AND tt_excl.TagID IS NULL
GROUP BY lt.ItemID
HAVING COUNT(DISTINCT tt.TagName) = @includedTagCount
ORDER BY lt.ItemID;

扩展：关联主表获取完整信息 #

如果需要获取主表的其他字段，可将上述查询作为子查询关联主表：

SET @myIncludedValues = REPLACE('Crimson, Violet', ' ', '');
SET @myExcludedValues = REPLACE('Khaki', ' ', '');
SET @includedTagCount = IF(CHAR_LENGTH(@myIncludedValues) > 0, 
                           CHAR_LENGTH(@myIncludedValues) - CHAR_LENGTH(REPLACE(@myIncludedValues, ',', '')) + 1, 
                           0);

SELECT m.*
FROM (
    SELECT lt.ItemID
    FROM linking_table lt
    JOIN tag_table tt ON lt.TagID = tt.TagID
    LEFT JOIN linking_table lt_excl ON lt.ItemID = lt_excl.ItemID
    LEFT JOIN tag_table tt_excl ON lt_excl.TagID = tt_excl.TagID 
        AND FIND_IN_SET(tt_excl.TagName, @myExcludedValues)
    WHERE FIND_IN_SET(tt.TagName, @myIncludedValues)
    AND tt_excl.TagID IS NULL
    GROUP BY lt.ItemID
    HAVING COUNT(DISTINCT tt.TagName) = @includedTagCount
) AS valid_items
JOIN main_table m ON valid_items.ItemID = m.ItemID
ORDER BY m.ItemID;

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