MySQL 8中如何匹配同一列中多个ID的精确集合？

表结构与示例数据 #

表t1包含自增主键id，以及substance_id（物质标识）和filter_id（过滤器标识）的映射关系，示例数据如下：

+---------+--------------+-----------+
| id      | substance_id | filter_id |
+---------+--------------+-----------+
| 7892022 |           26 |      2681 |
| 7892021 |          750 |      2680 |
| 7892020 |          750 |      2679 |
| 7892019 |          750 |      2677 |
| 7892018 |          750 |      2676 |
| 7892017 |          300 |      2680 |
| 7892016 |          300 |      2679 |
| 7892015 |           20 |      2681 |
| 7892014 |           20 |      2677 |
| 7892013 |            5 |      2681 |
| 7892012 |            5 |      2680 |
| 7892011 |            5 |      2679 |
| 7892010 |            5 |      2678 |
| 7892009 |            5 |      2677 |
| 7892008 |            5 |      2676 |

需求说明 #

编写查询语句，返回精确匹配指定filter_id集合的substance_id：即该substance_id关联的所有filter_id必须完全等于指定集合，排除部分匹配（包含指定集合但还有额外filter_id）或仅匹配部分指定filter_id的情况。

之前尝试的方法均不满足需求：

• 多AND条件（如filter_id=2681 AND filter_id=2677）返回0行，因为单条记录无法同时满足多个filter_id；
• 错误的AND写法返回部分匹配结果；
• IN()语句匹配任一指定filter_id，返回大量无关结果。

MySQL 8实现方案 #

完全可以实现，以下提供三种常用方法：

方法1：分组统计+条件过滤 #

假设要匹配的filter_id集合是{2681, 2677}，通过分组统计每个substance_id的filter_id数量，同时确保所有filter_id都在指定集合内，且数量与集合大小一致：

SELECT substance_id
FROM t1
GROUP BY substance_id
HAVING 
    COUNT(DISTINCT filter_id) = 2 -- 指定集合的元素个数
    AND SUM(CASE WHEN filter_id NOT IN (2681, 2677) THEN 1 ELSE 0 END) = 0;

• COUNT(DISTINCT filter_id) = 2确保该substance_id恰好关联2个不同的filter_id；
• SUM(CASE...) = 0确保没有超出指定集合的filter_id。

方法2：JSON函数聚合匹配（MySQL 8+支持） #

利用JSON_ARRAYAGG将每个substance_id的filter_id聚合为有序JSON数组，再与目标集合的JSON数组对比：

SELECT substance_id
FROM (
    SELECT 
        substance_id,
        JSON_ARRAYAGG(DISTINCT filter_id ORDER BY filter_id) AS filter_arr
    FROM t1
    GROUP BY substance_id
) AS sub
WHERE filter_arr = JSON_ARRAY(2677, 2681); -- 需与聚合时的排序顺序一致

聚合时通过ORDER BY filter_id固定数组元素顺序，对比时目标数组保持相同顺序即可确保匹配准确。

方法3：集合运算验证（MySQL 8.0.3+支持） #

通过EXCEPT和INTERSECT验证集合关系：每个substance_id的filter_id集合与指定集合的差集为空，且交集大小等于指定集合大小：

SELECT substance_id
FROM t1 t
GROUP BY substance_id
HAVING 
    NOT EXISTS (
        SELECT filter_id FROM t1 WHERE substance_id = t.substance_id
        EXCEPT
        SELECT 2681 UNION ALL SELECT 2677
    )
    AND (
        SELECT COUNT(*) FROM (
            SELECT filter_id FROM t1 WHERE substance_id = t.substance_id
            INTERSECT
            SELECT 2681 UNION ALL SELECT 2677
        ) AS temp
    ) = 2;

这种方法逻辑直观，适合复杂集合的匹配场景。

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