MySQL CTE 怎样写递归组织结构查询

组织架构、目录树和多级分类都可以抽象成一张“节点指向父节点”的表。MySQL 里最直接的写法是 WITH RECURSIVE：先取根节点，再把当前层的 id 与下一层的 parent_id 连接，直到连接不到新节点。实际使用时还要同时处理路径字段类型、深度上限和排序，否则很容易出现路径截断、无限递归或层级顺序混乱。

官方文档：https://dev.mysql.com/doc/refman/8.4/en/with.html

先把组织结构表和输出目标定清楚

假设表名为 departments，关键字段是 id、parent_id、name。根节点的 parent_id 使用 NULL，普通节点保存直接父节点 id。查询通常至少需要返回节点名称、层级 level 和从根节点开始的路径 path。

MySQL 递归 CTE 的核心写法：锚点查询接上子节点

下面的查询从所有根节点开始。第一段 SELECT 是锚点，不能引用 org_tree；第二段 SELECT 才引用 CTE，并通过 d.parent_id = t.id 找到下一层。UNION ALL 保留每一层生成的节点，外层再按路径排序。

WITH RECURSIVE org_tree (id, parent_id, name, level, path) AS (
    -- 锚点：从根节点开始，level 从 0 计数
    SELECT
        d.id,
        d.parent_id,
        d.name,
        0 AS level,
        CAST(d.name AS CHAR(512)) AS path
    FROM departments AS d
    WHERE d.parent_id IS NULL

    UNION ALL

    -- 递归：把当前节点 id 对上下一层的 parent_id
    SELECT
        child.id,
        child.parent_id,
        child.name,
        tree.level + 1,
        CONCAT(tree.path, ' / ', child.name)
    FROM org_tree AS tree
    JOIN departments AS child
      ON child.parent_id = tree.id
    -- 业务上限制最大层级，防止脏数据拖成长查询
    WHERE tree.level 

这里的 level 是业务边界示例，不代表所有组织结构都只能有 8 层。若需要从某个部门向下查询，把锚点条件改成 d.id = ?；若要向上找祖先，则应把递归连接方向改为当前节点的 parent_id 对上父节点的 id。

类型、停止条件和结果排序要一起设计

递归 CTE 的列类型只根据非递归的锚点 SELECT 推断。路径在递归部分不断追加文本，如果锚点中的字符串长度太短，严格模式可能报数据过长，非严格模式则可能截断。因此不要只写 CAST(d.name AS CHAR)，要根据最大深度和名称长度预留空间。

WITH RECURSIVE org_tree (id, name, level, path) AS (
    -- 显式给路径预留长度，避免递归拼接时发生截断
    SELECT id, name, 0, CAST(name AS CHAR(512))
    FROM departments
    WHERE parent_id IS NULL

    UNION ALL

    -- 递归列与锚点列按位置对应，类型边界由锚点决定
    SELECT d.id, d.name, t.level + 1,
           CONCAT(t.path, ' / ', d.name)
    FROM org_tree AS t
    JOIN departments AS d ON d.parent_id = t.id
    -- 没有更多子节点时自然停止，同时限制异常深度
    WHERE t.level 

停止条件至少有两层：一层是 JOIN 找不到子节点时自然结束，另一层是明确的深度或业务过滤条件。对可能存在环的表，不能只依赖“理论上不会成环”；可以在路径中保存节点 id 并在递归条件中排除已出现的 id，或在会话中配合 cte_max_recursion_depth 和 MAX_EXECUTION_TIME 限制风险。递归分支也不要放聚合、窗口函数、GROUP BY、ORDER BY 或普通 DISTINCT，需要汇总时放到外层。

上线前用四项检查确认结果可用

1. 检查根节点是否完整：没有根节点的孤立分支不会从当前锚点返回。
2. 检查层级是否连续：每个子节点的 level 应等于父节点加一，超过业务上限的节点要单独处理。
3. 检查排序字段：按名称排序适合阅读，按路径中的稳定 id 排序更适合接口返回；不要依赖数据库自然顺序。
4. 检查执行计划：使用 EXPLAIN 查看递归部分的访问路径，并为 parent_id 准备合适索引；递归 CTE 返回行数过大时，还要关注临时表内存压力。

如果查询只是展示树，不要把递归深度无限放大来掩盖脏数据。先确认是否有环、孤儿节点或错误的根节点，再决定是否调整上限。

常见问题

为什么忘记写 RECURSIVE 会提示找不到 CTE 表？

因为自引用 CTE 必须在 WITH 后声明 RECURSIVE。普通 CTE 可以省略这个关键字，但递归组织结构查询不能省略。

为什么 path 只返回了前几个字符？

锚点 SELECT 决定了路径列的类型和宽度。把根节点路径显式 CAST 成足够长的 CHAR 或合适的字符串类型，再检查是否处于严格 SQL 模式。

为什么结果没有按组织树顺序排列？

递归生成顺序不是展示顺序。返回路径或稳定的节点序列，并在最外层显式 ORDER BY；不要依赖没有排序时的自然顺序。