MySQL 一对多关联场景下如何实现关联表数据精确匹配查询

核心实现逻辑 #

无需使用GROUP_CONCAT做字符串拼接校验，通过「无多余关联项」+「匹配项数量完全一致」的双重校验即可实现需求，性能远高于字符串拼接方案。
假设待匹配的T2 id集合为y1、y2，去重后的集合大小为2，查询SQL如下：

SELECT t1_id
FROM T2 
GROUP BY t1_id 
HAVING 
    -- 校验：该t1_id关联的所有T2 id都在目标集合内，无多余项
    COUNT(CASE WHEN id NOT IN ('y1', 'y2') THEN 1 END) = 0
    -- 校验：关联的T2 id数量和目标集合大小完全一致，无缺失项
    AND COUNT(id) = 2

如果T2中同一个t1_id可能存在重复的id记录，将COUNT(id)替换为COUNT(DISTINCT id)即可。

方案优势 #

• 避免了字符串排序、拼接的CPU开销，执行效率更高
• 若T2建有(t1_id, id)联合索引，可直接走索引覆盖查询，无需回表，性能提升幅度可达数十倍
• 不存在GROUP_CONCAT长度限制导致的截断匹配错误问题，适配大数据量场景

高数据量场景优化写法 #

如果T2数据量极大，可以先过滤掉存在非目标id的t1_id，再做分组统计，进一步减少分组运算的数据量：

SELECT t1_id
FROM T2
WHERE t1_id NOT IN (
    SELECT DISTINCT t1_id FROM T2 WHERE id NOT IN ('y1', 'y2')
)
GROUP BY t1_id
HAVING COUNT(id) = 2

示例场景验证 #

完全符合要求的匹配规则：

• 查询目标为y1、y1时，去重后集合大小为1，COUNT(id)=2不满足，不会返回x1
• 查询目标为y1、y4时，x1关联的y2、x2关联的y5都不在目标集合，第一个校验条件不满足，无返回结果
• 查询目标为y1、y2时，x1的两个校验条件都满足，正常返回
• 查询目标为y4、y5时，x2的两个校验条件都满足，正常返回

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