请求优化MySQL查询以提取WooCommerce商品及变体数据

以下是优化后的查询语句，可同时获取普通商品、可变主商品及变体商品的完整信息：

SELECT 
    p.ID,
    p.post_title,
    p.guid,
    p.post_type,
    -- 可选字段：变体所属主商品ID，普通商品该值为NULL
    parent_meta.meta_value AS PARENT_PRODUCT_ID,
    COALESCE(sku_meta.meta_value, '') AS SKU,
    COALESCE(stock_meta.meta_value, 0) AS QTY,
    COALESCE(salesprice_meta.meta_value, 0) AS SALEPRICE,
    COALESCE(ean_meta.meta_value, '') AS EAN,
    COALESCE(regularprice_meta.meta_value, 0) AS RRP
FROM wp_posts p
LEFT JOIN wp_postmeta sku_meta ON p.ID = sku_meta.post_id AND sku_meta.meta_key = '_sku'
LEFT JOIN wp_postmeta stock_meta ON p.ID = stock_meta.post_id AND stock_meta.meta_key = '_stock'
LEFT JOIN wp_postmeta salesprice_meta ON p.ID = salesprice_meta.post_id AND salesprice_meta.meta_key = '_price'
LEFT JOIN wp_postmeta ean_meta ON p.ID = ean_meta.post_id AND ean_meta.meta_key = '_global_unique_id'
LEFT JOIN wp_postmeta regularprice_meta ON p.ID = regularprice_meta.post_id AND regularprice_meta.meta_key = '_regular_price'
-- 关联变体的父商品ID
LEFT JOIN wp_postmeta parent_meta ON p.ID = parent_meta.post_id AND parent_meta.meta_key = '_parent_id'
WHERE p.post_type IN ('product', 'product_variation')
-- 可选：仅获取已发布的有效商品
AND p.post_status = 'publish'

关键修改说明 #

• 覆盖变体商品：将WHERE条件的post_type扩展为IN ('product', 'product_variation')，直接读取WooCommerce中存储为product_variation类型的变体条目。
• 父商品关联：新增PARENT_PRODUCT_ID字段，通过_parent_id元数据关联变体所属的主商品，便于后续区分和关联主商品信息。
• 空值兼容：使用COALESCE函数将元数据的NULL值替换为合理默认值（如库存空时显示0，SKU空时显示空字符串），避免外部数据处理时出现异常。
• 状态过滤：可选添加post_status = 'publish'，仅返回已发布的商品，排除草稿、回收站等无效状态的条目。

扩展：获取变体属性信息 #

如果需要获取变体的属性（如颜色、尺寸），可添加对应元数据的关联，例如获取颜色属性：

-- 在JOIN部分添加
LEFT JOIN wp_postmeta color_meta ON p.ID = color_meta.post_id AND color_meta.meta_key = '_attribute_pa_color'

-- 在SELECT部分添加
COALESCE(color_meta.meta_value, '') AS COLOR

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