MySQL递归CTE展开树形数据

我第一次把组织架构从一张邻接表展开出来时，最容易写错的地方不是 JOIN，而是没有把“从哪里开始”和“什么时候停止”说清楚。MySQL 的递归 CTE 正好把这两个边界写在同一条查询里：锚点成员先取根节点，递归成员再根据父子关系不断补出下一层。

这篇只讨论常见的 id + parent_id 树形表。官方资料地址：https://dev.mysql.com/doc/refman/8.4/en/。目标是拿到节点、层级和可排序路径，并给递归查询加上深度与数据质量保护。

核心写法是 WITH RECURSIVE 加上“根节点查询 UNION ALL 子节点查询”。根节点决定初始结果和列类型，递归成员通过 child.parent_id = parent.id 连接下一层，外层查询再负责筛选和排序。

先把树形表和递归成员对齐

假设表结构如下。这里的 parent_id 允许为空，空值表示根节点；实际项目中最好给它建立普通索引，让递归 JOIN 能按父节点查找子节点。

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    parent_id INT NULL,
    INDEX idx_employees_parent_id (parent_id)
);

-- 示例数据只表达父子关系，不依赖具体业务名称
INSERT INTO employees (id, name, parent_id) VALUES
    (1, '总部', NULL),
    (2, '研发部', 1),
    (3, '平台组', 2),
    (4, '应用组', 2),
    (5, '市场部', 1);

递归 CTE 中的两个 SELECT 各有明确职责：第一个是非递归的锚点成员，第二个引用 CTE 自身并产生下一批节点。它们通过 UNION ALL 合并，递归成员本身不要写成带聚合或排序的报表查询。

用 WITH RECURSIVE 展开所有下级节点

下面的查询把根节点、层级和路径一起带出来。路径不是为了展示好看，而是给后续的树状排序和子树筛选提供稳定依据。

WITH RECURSIVE employee_tree (id, name, parent_id, depth, path) AS (
    -- 锚点成员：从没有父节点的根记录开始，深度从 0 起算
    SELECT
        e.id,
        e.name,
        e.parent_id,
        0 AS depth,
        CAST(e.id AS CHAR(200)) AS path
    FROM employees AS e
    WHERE e.parent_id IS NULL

    UNION ALL

    -- 递归成员：把当前节点的 id 对上下一层记录的 parent_id
    SELECT
        child.id,
        child.name,
        child.parent_id,
        parent.depth + 1,
        CONCAT(parent.path, '/', child.id)
    FROM employee_tree AS parent
    JOIN employees AS child
      ON child.parent_id = parent.id
)
SELECT id, name, parent_id, depth, path
FROM employee_tree
ORDER BY path;

这里的关键细节有两个。第一，递归列的类型主要由锚点 SELECT 推断，所以路径列在锚点里直接用 CAST 预留长度；否则递归阶段拼接更长字符串时，可能出现截断或严格模式错误。第二，ORDER BY path 放在最外层，递归成员只负责产生关系，不负责展示顺序。

从完整树中取一棵子树

如果页面只需要展示“研发部”下面的节点，可以保留完整路径，再用路径前缀或根节点 ID 做外层条件。对整数 ID 来说，路径前缀要避免把 2 误匹配到 20，因此建议使用分隔符。

WITH RECURSIVE employee_tree (id, name, parent_id, depth, path) AS (
    -- 只把指定部门作为本次展开的根，减少无关分支
    SELECT
        e.id,
        e.name,
        e.parent_id,
        0 AS depth,
        CAST(CONCAT('/', e.id, '/') AS CHAR(200)) AS path
    FROM employees AS e
    WHERE e.id = 2

    UNION ALL

    -- 继续沿 parent_id 找到该部门的所有下级
    SELECT
        child.id,
        child.name,
        child.parent_id,
        parent.depth + 1,
        CONCAT(parent.path, child.id, '/')
    FROM employee_tree AS parent
    JOIN employees AS child
      ON child.parent_id = parent.id
)
SELECT id, name, depth, path
FROM employee_tree
WHERE depth 

把根节点条件放在锚点里，通常比先展开全表再过滤更容易控制结果集。若业务需要“从任意节点向上找祖先”，连接方向要反过来：让当前节点的 parent_id 去匹配上层记录的 id，不要照搬向下展开的 JOIN。

深度、叶子节点和路径长度要单独处理

递归结果里保留 depth 后，目录树、组织架构和评论回复都可以用同一列做限制。叶子节点则要看它是否还能在原表中找到子节点。

WITH RECURSIVE employee_tree (id, name, parent_id, depth, path) AS (
    -- 锚点列显式给 path 留出足够宽度
    SELECT id, name, parent_id, 0, CAST(CONCAT('/', id, '/') AS CHAR(200))
    FROM employees
    WHERE parent_id IS NULL

    UNION ALL

    -- 递归阶段只做父子连接和字段扩展
    SELECT child.id, child.name, child.parent_id,
           parent.depth + 1,
           CONCAT(parent.path, child.id, '/')
    FROM employee_tree AS parent
    JOIN employees AS child ON child.parent_id = parent.id
)
SELECT t.id, t.name, t.depth, t.path,
       CASE WHEN EXISTS (
           -- 存在直接子节点时不是叶子节点
           SELECT 1 FROM employees AS c WHERE c.parent_id = t.id
       ) THEN 0 ELSE 1 END AS is_leaf
FROM employee_tree AS t
WHERE t.depth 

这里的 depth 是业务保护，不是 MySQL 的全局递归上限。对路径长度不可预测的系统，应该按最大层数估算 CHAR 宽度，或者改用单独的路径模型，而不是让字符串无限增长。

递归查询为什么会失控

最常见原因是递归成员没有真正缩小问题：例如父子数据出现环，或者连接条件把已经访问过的节点重新接回结果。开发阶段可以用递归深度变量和查询超时做保护，线上仍应从写入侧保证树没有环。

-- 只影响当前会话，先给深度设置一个保守上限
SET SESSION cte_max_recursion_depth = 100;

-- 给当前 SELECT 设置毫秒级执行时间上限
SET SESSION max_execution_time = 1000;

-- 查询内部也可以用 MAX_EXECUTION_TIME 做单条语句保护
WITH RECURSIVE employee_tree (id, parent_id, depth) AS (
    -- 锚点从根节点开始
    SELECT id, parent_id, 0
    FROM employees
    WHERE parent_id IS NULL
    UNION ALL
    -- 递归成员必须有明确的父子连接
    SELECT child.id, child.parent_id, parent.depth + 1
    FROM employee_tree AS parent
    JOIN employees AS child ON child.parent_id = parent.id
)
SELECT /*+ MAX_EXECUTION_TIME(1000) */ id, parent_id, depth
FROM employee_tree;

不要把提高 cte_max_recursion_depth 当作修复循环数据的方法。它只是在确认数据合法且层级确实很深时，给查询更多空间；如果递归条件本身有问题，应先修复数据约束和连接条件。

执行计划和数据模型怎么取舍

递归 CTE 适合层级深度有限、读请求需要即时反映父子变化的场景。parent_id 上的索引是基础配置；查询量变大后，可以用 EXPLAIN 观察递归成员对下级节点的访问方式。MySQL 文档说明递归 CTE 会物化，结果集过大时还可能增加内部临时表开销。

-- 只查看计划，不改变数据；重点关注 parent_id 的访问路径
EXPLAIN
WITH RECURSIVE employee_tree (id, name, parent_id, depth) AS (
    -- 读取根节点
    SELECT id, name, parent_id, 0
    FROM employees
    WHERE parent_id IS NULL
    UNION ALL
    -- 通过 parent_id 找下一层
    SELECT child.id, child.name, child.parent_id, parent.depth + 1
    FROM employee_tree AS parent
    JOIN employees AS child ON child.parent_id = parent.id
)
SELECT id, name, depth
FROM employee_tree;

如果树很深、同一棵子树被频繁读取，或者层级查询要参与大量聚合，闭包表、物化路径或预计算层级可能更合适。它们会增加写入和维护成本，但能把重复递归的读取代价换成可索引的普通查询。

最后记住这张检查表

• 锚点成员先选根节点，递归成员只连接下一层。
• 递归列的长度由锚点推断，路径字符串要提前 CAST。
• 递归成员不要放聚合、窗口函数、GROUP BY 或 ORDER BY。
• 用 depth 限制业务结果，用 cte_max_recursion_depth 和超时防止失控。
• 给 parent_id 建索引；读压力大时再评估物化路径或闭包表。

如果你的需求只是“从某个节点拿所有下级”，上面的递归 CTE 已经够用；如果还要高频做祖先查询、子树统计和跨层聚合，先比较写入维护成本，再决定是否升级数据模型。