MySQL双表动态元数据查询方案咨询：适配新增字段无需改SQL

针对你遇到的动态元数据查询需求，以下是几个无需修改基础SQL结构即可适配的可行方案：

方案一：应用层动态拼接SQL #

这是最灵活且易维护的方案，核心逻辑在应用代码中处理：

• 字段拼接：SELECT部分固定主表字段，根据传入的元数据参数，动态追加MAX(CASE WHEN pm.meta_key = 'color' THEN pm.meta_value END) AS color这类语句，新增元数据只需在参数中添加对应key即可。
• 条件拼接：WHERE部分根据参数生成过滤逻辑，比如查询color=red就追加AND EXISTS (SELECT 1 FROM corewp_productmeta pm1 WHERE pm1.product_id = p.id AND pm1.meta_key = 'color' AND pm1.meta_value = 'red')；价格区间则处理数值转换：AND EXISTS (SELECT 1 FROM corewp_productmeta pm2 WHERE pm2.product_id = p.id AND pm2.meta_key = 'price' AND CAST(pm2.meta_value AS DECIMAL(10,2)) BETWEEN 100 AND 200)。
• 分页处理：直接在SQL末尾拼接LIMIT offset, size即可，不受动态字段影响。
• 注意：必须用预编译语句绑定参数，杜绝SQL注入风险。

方案二：利用JSON函数聚合元数据（MySQL 5.7+） #

通过将单产品的元数据聚合为JSON对象，实现动态字段提取与过滤：

SELECT 
  p.id,
  p.product_name, -- 主表其他固定字段
  JSON_EXTRACT(pm_meta.meta_data, '$.color') AS color,
  JSON_EXTRACT(pm_meta.meta_data, '$.price') AS price,
  JSON_EXTRACT(pm_meta.meta_data, '$.brand') AS brand
FROM corewp_product p
LEFT JOIN (
  SELECT 
    product_id,
    JSON_OBJECTAGG(meta_key, meta_value) AS meta_data
  FROM corewp_productmeta
  GROUP BY product_id
) pm_meta ON p.id = pm_meta.product_id
WHERE 
  -- 空参数时跳过该条件，避免过滤无对应元数据的产品
  (JSON_EXTRACT(pm_meta.meta_data, '$.color') = 'red' OR 'red' IS NULL)
  AND (CAST(JSON_EXTRACT(pm_meta.meta_data, '$.price') AS DECIMAL(10,2)) BETWEEN 100 AND 200 OR 100 IS NULL)
LIMIT 0, 10;

• 优势：无需修改SQL结构，新增元数据只需在SELECT和WHERE中添加对应JSON_EXTRACT语句，应用层仅需替换参数key。
• 注意：给corewp_productmeta的product_id和meta_key加联合索引优化聚合性能；数值型元数据要做好类型转换。

方案三：存储过程封装动态逻辑 #

把动态SQL生成逻辑封装在MySQL存储过程中，应用层直接调用传参：

DELIMITER //
CREATE PROCEDURE GetProducts(IN params JSON, IN offset INT, IN limit INT)
BEGIN
  DECLARE select_clause VARCHAR(1000) DEFAULT 'p.id, p.product_name';
  DECLARE where_clause VARCHAR(1000) DEFAULT '1=1';
  
  -- 动态拼接SELECT字段
  IF JSON_CONTAINS_PATH(params, 'one', '$.color') THEN
    SET select_clause = CONCAT(select_clause, ', MAX(CASE WHEN pm.meta_key = ''color'' THEN pm.meta_value END) AS color');
  END IF;
  -- 新增元数据时只需复制上述逻辑修改key即可
  
  -- 动态拼接WHERE条件
  IF JSON_VALUE(params, '$.color') IS NOT NULL THEN
    SET where_clause = CONCAT(where_clause, ' AND EXISTS (SELECT 1 FROM corewp_productmeta pm1 WHERE pm1.product_id = p.id AND pm1.meta_key = ''color'' AND pm1.meta_value = ''', JSON_VALUE(params, '$.color'), ''')');
  END IF;
  
  -- 执行动态SQL
  SET @sql = CONCAT(
    'SELECT ', select_clause, 
    ' FROM corewp_product p LEFT JOIN corewp_productmeta pm ON p.id = pm.product_id ',
    ' WHERE ', where_clause,
    ' GROUP BY p.id ',
    ' LIMIT ', offset, ', ', limit
  );
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

• 优势：动态逻辑集中在数据库层，应用层调用简单。
• 注意：做好参数校验防止SQL注入；存储过程后续维护成本较高。

内容的提问来源于stack exchange，提问作者Josh L. Minga