MySQL8不使用UNION查询层级结构完整血缘（祖先+后代+自身）的实现方案

可行性结论 #

完全可以不用UNION合并两个递归查询实现目标需求，仅需1次递归CTE即可同时完成向上查祖先、向下查后代的逻辑，性能还会优于双CTE合并的方案。

实现思路 #

在递归CTE中新增遍历方向标记字段，区分当前递归分支是向上找祖先、还是向下找后代，初始节点同时开启两个方向的遍历，避免重复执行递归逻辑：

• 初始节点：目标节点本身，标记为未开始遍历的初始状态
• 向上递归分支：只要当前节点存在父节点，就继续向上查询所有祖先
• 向下递归分支：只要当前节点存在子节点，就继续向下查询所有后代

完整实现代码 #

WITH RECURSIVE category_relation AS (
    -- 初始节点：目标节点C2
    SELECT 
        id, 
        parent_id,
        0 AS traversal_dir -- 遍历方向：0=初始节点，1=向上找祖先，-1=向下找后代
    FROM categories
    WHERE id = 'C2'
    
    UNION ALL
    
    -- 递归部分1：向上找祖先
    SELECT 
        t.id,
        t.parent_id,
        1 AS traversal_dir
    FROM category_relation c
    INNER JOIN categories t ON t.id = c.parent_id
    WHERE c.traversal_dir IN (0, 1) -- 只有初始节点/向上分支的节点才继续查祖先
      AND c.parent_id IS NOT NULL -- 父节点为空时停止向上遍历
      
    UNION ALL
    
    -- 递归部分2：向下找后代
    SELECT
        t.id,
        t.parent_id,
        -1 AS traversal_dir
    FROM category_relation c
    INNER JOIN categories t ON t.parent_id = c.id
    WHERE c.traversal_dir IN (0, -1) -- 只有初始节点/向下分支的节点才继续查后代
)
-- 最终去重取结果，避免初始节点重复
SELECT DISTINCT id, parent_id 
FROM category_relation;

执行结果验证 #

使用你提供的示例数据执行上述SQL，输出结果完全匹配预期：

id  parent_id
C2  B2
A1  null
B2  A1
D3  C2
D4  C2
D5  C2
E1  D5

原UNION版本问题说明 #

你之前写的UNION版本存在parent_id错误，是因为向上递归的部分错误交换了id和parent_id的取值，把父节点的id作为返回值的id、同时把当前CTE的id作为parent_id，导致层级关系反转。

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