MySQL如何查询关联products_meta满足多条件的products表数据

这是EAV（实体-属性-值）结构表做多属性条件匹配的典型场景，核心思路是先定位到同时满足两个元数据规则的产品ID，再关联拉取对应产品的主表信息和全量元数据即可，以下是3种可直接运行的写法：

方案1：聚合筛选（通用性最强，性能稳定） #

通过分组聚合判断产品是否同时命中两个属性条件，适合属性条件多的场景，扩展条件只需要修改WHERE里的规则和HAVING的计数即可：

-- 一次查询返回符合条件的产品+对应全量元数据
SELECT p.*, pm.*
FROM products p
INNER JOIN products_meta pm 
  ON p.id = pm.product_id
INNER JOIN (
  SELECT product_id
  FROM products_meta
  WHERE
    (`key` = 'status' AND `value` = 'enabled')
    OR (`key` = 'availability' AND CAST(`value` AS UNSIGNED) > 0)
  GROUP BY product_id
  HAVING COUNT(DISTINCT `key`) = 2 -- 确保两个属性条件全部命中
) AS valid_product
  ON p.id = valid_product.product_id;

注意点：

• key是MySQL保留关键字，写查询时必须加反引号包裹，否则会报语法错误
• value字段是字符串类型，判断availability数值大小时需要先转成数值类型，否则会按字符串字典序比较，出现比如'100' < '2'的逻辑错误

方案2：双JOIN匹配（适合少量条件场景） #

通过两次关联元数据表分别匹配两个规则，不需要做分组聚合，逻辑直观：

SELECT p.*, pm_all.*
FROM products p
-- 匹配status=enabled条件
INNER JOIN products_meta pm_status
  ON p.id = pm_status.product_id
  AND pm_status.`key` = 'status'
  AND pm_status.`value` = 'enabled'
-- 匹配availability>0条件
INNER JOIN products_meta pm_stock
  ON p.id = pm_stock.product_id
  AND pm_stock.`key` = 'availability'
  AND CAST(pm_stock.`value` AS UNSIGNED) > 0
-- 关联拉取该产品的全量元数据
INNER JOIN products_meta pm_all
  ON p.id = pm_all.product_id;

方案3：IN子查询改写（适配原有写法习惯） #

直接在原有IN逻辑基础上追加第二个IN条件，取两个结果集的交集即可：

SELECT p.*, pm.*
FROM products p
INNER JOIN products_meta pm
  ON p.id = pm.product_id
WHERE p.id IN (
  SELECT product_id FROM products_meta WHERE `key` = 'status' AND `value` = 'enabled'
)
AND p.id IN (
  SELECT product_id FROM products_meta WHERE `key` = 'availability' AND CAST(`value` AS UNSIGNED) > 0
);

执行结果验证 #

以上三种写法执行后，返回的产品主表记录为：

id | created_at | updated_at
----------------------------
1  | 10-06-2022 | 11-06-2022

返回的对应产品元数据记录为：

id | product_id | key          | value
-----------------------------------------
1  | 1          | status       | enabled
2  | 1          | availability | 50
3  | 1          | name         | Bike

完全匹配预期输出。

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