MySQL递归CTE中的内连接理解困惑及问题咨询

数据定义 #

CREATE TABLE
categories (
    id int,
    cat_name varchar(100),
    parent_category_id int DEFAULT NULL
);

INSERT INTO
    categories
VALUES
    (1, 'Mens', NULL),
    (2, 'Tops', 1),
    (3, 'Jerseys', 2),
    (4, 'England', 3);

查询代码 #

WITH RECURSIVE
category_tree AS (
    SELECT
        id,
        cat_name,
        parent_category_id,
        cat_name AS full_name
    FROM categories
    WHERE parent_category_id IS NULL                
    UNION ALL                                       
    SELECT
        c.id,                                       
        c.cat_name,                                 
        c.parent_category_id,
        CONCAT (ct.full_name, ' > ', c.cat_name)
    FROM categories c
        JOIN category_tree ct 
        ON c.parent_category_id = ct.id             
)
SELECT
    id, full_name
FROM
    category_tree;

表数据与查询结果 #

categories表数据 #

查询结果 #

疑问解答 #

你误解了递归CTE的执行逻辑，category_tree不是只包含初始的id=1记录，而是每次递归都会把新生成的记录加入CTE集合，具体执行步骤如下：

1. 初始锚点查询：
   执行WHERE parent_category_id IS NULL的查询，得到第一条记录：

   id	cat_name	parent_category_id	full_name
   1	Mens	NULL	Mens

   此时category_tree仅包含这条记录。

2. 第一次递归：
   用categories表和当前的category_tree（仅id=1的记录）做JOIN，匹配c.parent_category_id = ct.id，找到parent_category_id=1的记录（id=2的Tops），生成新记录：

   id	cat_name	parent_category_id	full_name
   2	Tops	1	Mens > Tops

   现在category_tree包含初始记录+这条新记录，共2条。

3. 第二次递归：
   用categories表和当前的category_tree（含id=1、2的记录）做JOIN：

   • ct.id=1时，匹配到的id=2记录已处理；
   • ct.id=2时，匹配到parent_category_id=2的记录（id=3的Jerseys），生成新记录：

     id	cat_name	parent_category_id	full_name
     3	Jerseys	2	Mens > Tops > Jerseys

   现在category_tree新增这条记录，共3条。

4. 第三次递归：
   用categories表和当前的category_tree（含id=1、2、3的记录）做JOIN：

   • ct.id=3时，匹配到parent_category_id=3的记录（id=4的England），生成新记录：

     id	cat_name	parent_category_id	full_name
     4	England	3	Mens > Tops > Jerseys > England

   现在category_tree新增这条记录，共4条。

5. 递归终止：
   再次执行递归查询时，categories表中没有未处理过的、parent_category_id匹配category_tree中任何id的记录，递归停止。

简言之，ct.id不是始终只有1，而是每次递归都会用上一轮整个CTE集合的所有记录去匹配，从而逐层遍历出完整的分类树。

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