内连接搭配递归子查询无返回结果问题求助

数据表结构 #

product(id)

product_locale(product_id, locale_id)

category_product(category_id, product_id)

需求 #

查询某一分类（ID=17）及其所有子分类下的全部产品，分类结构为 17 > 19 > 33 > 35。

原查询语句 #

SELECT a.id,
       a.added,
       a.seller,
       a.position,
       c.title,
       c.alias
FROM product a
INNER JOIN product_locale c ON a.id = c.product_id
INNER JOIN category_product d ON d.product_id = a.id
WHERE c.locale_id = 1
  AND (d.category_id = 17
       OR d.category_id in
         (SELECT id
          FROM
            (SELECT *
             FROM category
             ORDER BY parent,
                      id) category_sorted,

            (SELECT @pv := 17) initialisation
          WHERE find_in_set(parent, @pv)
            AND length(@pv := concat(@pv, ',', id))))
LIMIT 0,
      10

递归子查询（单独执行正常） #

SELECT id
FROM
  (SELECT *
   FROM category
   ORDER BY parent,
            id) category_sorted,

  (SELECT @pv := 17) initialisation
WHERE find_in_set(parent, @pv)
  AND length(@pv := concat(@pv, ',', id))

问题现象 #

• 单独运行递归子查询可得到正确结果：19、33、35
• 将子查询结果直接填入主查询的IN子句中，可正常返回数据
• 使用子查询时，主查询无记录返回
• 将product与product_locale的内连接改为左连接，并将c.locale_id = 1放在ON子句中，查询可正常运行；但将该条件移至WHERE子句时，查询再次失效

原因分析 #

1. 用户变量执行时机问题：MySQL中用户变量@pv的初始化和更新逻辑在嵌套子查询中可能不按预期执行。主查询的WHERE子句执行时，可能先于子查询的变量初始化，导致子查询无法正确递归获取子分类ID，返回空集。此时主查询条件等价于d.category_id = 17 OR d.category_id IN ()，仅筛选分类17的产品，若这些产品无匹配locale_id=1的记录，内连接后就无结果。
2. 内连接与WHERE条件的过滤顺序：内连接会先匹配product和product_locale的记录，再应用WHERE中的c.locale_id=1条件；而左连接+ON子句的条件是在连接阶段就过滤product_locale，保留所有符合分类条件的产品（即使无对应locale记录），因此能返回结果，但这并非解决子查询失效的根本办法。

解决办法 #

方法1：使用CTE递归查询（推荐，MySQL 8.0+支持） #

改用MySQL原生的递归CTE替代用户变量实现子分类查询，避免变量作用域和执行顺序问题：

WITH RECURSIVE category_tree AS (
    -- 初始节点：目标分类
    SELECT id FROM category WHERE id = 17
    UNION ALL
    -- 递归获取子分类
    SELECT c.id 
    FROM category c
    JOIN category_tree ct ON c.parent = ct.id
)
SELECT a.id,
       a.added,
       a.seller,
       a.position,
       c.title,
       c.alias
FROM product a
INNER JOIN product_locale c ON a.id = c.product_id AND c.locale_id = 1
INNER JOIN category_product d ON d.product_id = a.id
INNER JOIN category_tree ct ON d.category_id = ct.id
LIMIT 0, 10;

方法2：使用临时表存储子分类ID #

先将递归子查询的结果存入临时表，再在主查询中引用，确保子分类ID能正确获取：

-- 创建临时表并插入子分类ID
CREATE TEMPORARY TABLE temp_categories AS
SELECT id
FROM
  (SELECT * FROM category ORDER BY parent, id) category_sorted,
  (SELECT @pv := 17) initialisation
WHERE find_in_set(parent, @pv) AND length(@pv := concat(@pv, ',', id));

-- 主查询引用临时表
SELECT a.id,
       a.added,
       a.seller,
       a.position,
       c.title,
       c.alias
FROM product a
INNER JOIN product_locale c ON a.id = c.product_id
INNER JOIN category_product d ON d.product_id = a.id
WHERE c.locale_id = 1
  AND d.category_id IN (17, SELECT id FROM temp_categories)
LIMIT 0, 10;

-- 用完临时表可删除（可选）
DROP TEMPORARY TABLE temp_categories;

方法3：调整连接顺序 #

先获取符合分类条件的产品，再关联多语言信息，减少条件冲突：

SELECT a.id,
       a.added,
       a.seller,
       a.position,
       c.title,
       c.alias
FROM (
    SELECT p.id, p.added, p.seller, p.position
    FROM product p
    INNER JOIN category_product d ON p.id = d.product_id
    WHERE d.category_id = 17
       OR d.category_id IN (
           SELECT id
           FROM
             (SELECT * FROM category ORDER BY parent, id) category_sorted,
             (SELECT @pv := 17) initialisation
           WHERE find_in_set(parent, @pv) AND length(@pv := concat(@pv, ',', id))
       )
) a
INNER JOIN product_locale c ON a.id = c.product_id AND c.locale_id = 1
LIMIT 0, 10;

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