MySQL/MariaDB实现跨多行多条件匹配的where查询方法

现有存储产品属性信息的Products表采用EAV（实体-属性-值）模型设计，表结构与示例数据如下：

Table Products
ad_id| property_id | property_value_id
69          4           1
69          7           6
69          6           3
67          7           6
...

查询同时满足多组属性键值匹配规则的ad_id：例如匹配规则为「property_id = 4 且 property_value_id = 1、property_id = 7 且 property_value_id = 6两个条件同时成立」时，预期返回结果为69。实际业务场景中待匹配的属性键值对为动态传入，需要通用可扩展的SQL实现方案。

避坑提示：不要直接在WHERE子句中用AND拼接多组属性条件，单条记录的property_id字段只能存储一个值，不可能同时等于4和7，这种写法永远返回空结果。

优先选用分组聚合+条件计数方案，动态传参拼接成本最低，适配任意数量的属性匹配条件：

SELECT ad_id
FROM Products
WHERE 
  -- 此处拼接所有传入的属性键值对条件，用OR连接
  (property_id = 4 AND property_value_id = 1)
  OR (property_id = 7 AND property_value_id = 6)
GROUP BY ad_id
-- 等号右侧的数值等于传入的属性条件总组数即可，本例共2组条件所以写2
HAVING COUNT(DISTINCT property_id, property_value_id) = 2;

动态适配规则 #

传入N组属性键值对时，仅需要修改两处：

• 在WHERE子句中追加所有用OR连接的(property_id = ? AND property_value_id = ?)条件块
• 将HAVING子句中等号右侧的数值改为传入的条件总组数N

如果业务中不存在同一个ad_id下相同property_id对应多个property_value_id的情况，HAVING里的计数可以简化为COUNT(DISTINCT property_id) = N，执行效率更高。

如果匹配条件固定为少量几组，也可以用表自连接实现，在存在联合索引时性能表现较好：

SELECT p1.ad_id
FROM Products p1
INNER JOIN Products p2 ON p1.ad_id = p2.ad_id
WHERE p1.property_id = 4 AND p1.property_value_id = 1
  AND p2.property_id = 7 AND p2.property_value_id = 6;

如果需要匹配3组及以上条件，对应多JOIN一次Products表即可，但动态场景下拼接JOIN逻辑的复杂度远高于聚合方案，不推荐动态业务使用。

性能优化建议：给Products表建立联合索引idx_ad_prop_val(ad_id, property_id, property_value_id)，可以避免全表扫描，将上述两类查询的性能提升数个量级。

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