MySQL CTE递归公共表表达式实现树形结构查询

在关系型数据库中处理层级数据（如组织架构、分类目录、评论回复）一直是开发中的痛点。MySQL 8.0 引入了递归公用表表达式，让这种查询变得简单且高效。本文将手带你通过三个典型场景，掌握从任意节点向下查询子节点、向上查询父节点以及生成完整路径的方法。

准备工作：构建测试环境

为了演示具体操作，首先需要建立一个包含父子关系的测试表并填充数据。

1. 创建一个名为 department 的表，包含 id、name 和 parent_id 字段。
2. 执行以下 SQL 语句初始化表结构。

CREATE TABLE department (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    parent_id INT DEFAULT NULL
);

3. 插入模拟数据，构建一个三层级的树形结构：总部下设两个部门，其中一个部门下设有小组。

INSERT INTO department (id, name, parent_id) VALUES
(1, '集团总部', NULL),
(2, '研发中心', 1),
(3, '市场部', 1),
(4, '后端组', 2),
(5, '前端组', 2),
(6, '华北区', 3);

此时数据逻辑如下：

• 1 是根节点。
• 2 和 3 是 1 的子节点。
• 4 和 5 是 2 的子节点。
• 6 是 3 的子节点。

场景一：自顶向下查询（查找所有子节点）

需求：给定一个父节点 ID（例如“研发中心”，ID=2），查出它所有的下级部门（包括子节点的子节点）。

递归 CTE 的核心逻辑包含两部分：锚点成员（初始查询）和 递归成员（循环查询）。

1. 定义 CTE 名称，例如 sub_tree。
2. 编写锚点查询：选择初始节点（ID=2）。
3. 编写递归查询：关联 department 表与 CTE 自身，条件是 department 的 parent_id 等于 CTE 中的 id。
4. 执行最终的 SELECT 语句获取结果。

WITH RECURSIVE sub_tree AS (
    -- 1. 锚点成员：查找起始节点
    SELECT id, name, parent_id
    FROM department
    WHERE id = 2

    UNION ALL

    -- 2. 递归成员：查找下一层
    SELECT d.id, d.name, d.parent_id
    FROM department d
    INNER JOIN sub_tree s ON d.parent_id = s.id
)
-- 3. 最终查询
SELECT * FROM sub_tree;

执行逻辑流程图：

查询结果展示：

场景二：自底向上查询（查找所有祖先节点）

需求：给定一个底层节点 ID（例如“后端组”，ID=4），查出它的所有上级部门，直到根节点。

这与向下查询的逻辑相反，关联条件需要反转：用 CTE 中的 parent_id 去匹配 department 表中的 id。

1. 定义 CTE 名称 path_to_root。
2. 编写锚点查询：选择目标节点（ID=4）。
3. 编写递归查询：关联 department 表与 CTE，条件是 CTE 的 parent_id 等于 department 的 id。
4. 执行查询。

WITH RECURSIVE path_to_root AS (
    -- 1. 锚点成员：从底层节点开始
    SELECT id, name, parent_id
    FROM department
    WHERE id = 4

    UNION ALL

    -- 2. 递归成员：向上查找父节点
    SELECT d.id, d.name, d.parent_id
    FROM department d
    INNER JOIN path_to_root p ON d.id = p.parent_id
)
SELECT * FROM path_to_root;

查询结果展示：

场景三：显示完整路径与层级深度

在实际业务中（如显示面包屑导航），通常需要展示从根节点到当前节点的完整路径，或者计算节点的深度。这需要在递归过程中累加字符串和数值。

1. 定义 CTE 名称 full_tree。
2. 初始化字段：在锚点查询中，设置 depth 为 1，设置 path 为节点名称。
3. 更新字段：在递归查询中，将 depth 加 1，使用 CONCAT 函数拼接路径（例如：父路径 > 当前名称）。
4. 执行查询并按深度排序。

WITH RECURSIVE full_tree AS (
    -- 1. 锚点成员：初始化路径和深度
    SELECT 
        id, 
        name, 
        parent_id, 
        1 AS depth, 
        CAST(name AS CHAR(1000)) AS path
    FROM department
    WHERE parent_id IS NULL -- 假设从根节点开始构建整棵树

    UNION ALL

    -- 2. 递归成员：累加路径和深度
    SELECT 
        d.id, 
        d.name, 
        d.parent_id, 
        ft.depth + 1, 
        CONCAT(ft.path, ' > ', d.name) AS path
    FROM department d
    INNER JOIN full_tree ft ON d.parent_id = ft.id
)
SELECT * FROM full_tree ORDER BY depth, name;

查询结果展示：

常见问题与优化技巧

在使用递归查询时，必须注意以下几个关键点，以避免性能问题或错误。

1. 添加索引：确保 parent_id 字段上有索引。这是递归查询性能的核心，没有索引，每一次递归迭代都会导致全表扫描。
2. 限制递归深度：如果数据存在循环引用（例如 A 的父节点是 B，B 的父节点又是 A），查询会陷入死循环。MySQL 默认限制递归深度为 1000。你可以通过 SET SESSION cte_max_recursion_depth = 1000000; 临时调整，或者更安全地做法是在 WHERE 子句中添加深度限制。

添加深度限制的示例：

WITH RECURSIVE safe_tree AS (
    SELECT id, name, parent_id, 1 AS level
    FROM department WHERE id = 1
    UNION ALL
    SELECT d.id, d.name, d.parent_id, s.level + 1
    FROM department d
    JOIN safe_tree s ON d.parent_id = s.id
    WHERE s.level < 10 -- 强制限制递归层级不超过10层
)
SELECT * FROM safe_tree;

3. 处理类型转换：在拼接路径字符串时（如场景三），务必使用 CAST(... AS CHAR(...)) 显式指定类型，否则 MySQL 可能会因为字段类型推断错误（如将 path 判别为 TEXT/BLOB 导致无法排序）而报错。