MySQL CTE 递归查询组织层级的终止条件

我第一次用 MySQL 递归 CTE 展开员工组织树时，最容易混淆的是“什么时候应该结束”和“最多允许跑多久”。正确的写法不是只把 cte_max_recursion_depth 调大，而是让递归成员在找不到下属时自然产生空结果，再用行数、递归层数和执行时间做兜底。

组织层级查询的业务终止条件应写在递归成员的关联关系里：当前行没有下属，就不再产生下一轮数据；LIMIT、cte_max_recursion_depth 和执行超时只负责限制异常消耗，不能替代这条业务逻辑。

• 起点：锚点成员先选出 CEO 或指定部门负责人。
• 自然结束：递归成员通过 child.manager_id = parent.employee_id 找下一层，叶子节点匹配不到子行就停止。
• 安全边界：再按查询场景设置递归行数、递归深度和执行时间上限。

先把递归 CTE 拆成两块

组织树通常有一张 employees 表，其中 employee_id 是员工主键，manager_id 指向直属上级。递归 CTE 的第一段是锚点成员，负责放入起始员工；第二段是递归成员，只从上一轮得到的员工继续连接下一层。

下面的示例把 manager_id IS NULL 的 CEO 作为根节点，并保留一个 depth 字段观察层数。SQL 里的注释解释的是示例逻辑，不代表已经在某个线上库执行过。

WITH RECURSIVE org_tree AS (
    -- 锚点成员：从没有直属上级的 CEO 开始
    SELECT employee_id, manager_id, name, 0 AS depth
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- 递归成员：只接上上一轮员工的直属下属
    SELECT child.employee_id, child.manager_id, child.name, parent.depth + 1
    FROM employees AS child
    JOIN org_tree AS parent
      ON child.manager_id = parent.employee_id
)
SELECT employee_id, manager_id, name, depth
FROM org_tree
ORDER BY depth, employee_id;

把叶子节点写成自然终止条件

上面的 JOIN 本身就是第一道终止条件。假设某个员工没有直属下属，那么以他为 parent 时，child.manager_id = parent.employee_id 找不到新行，这一轮递归结果为空，CTE 就不会继续生成该分支。

这比单独写一个“当前深度小于 10”更准确，因为深度不是组织关系的真实终点。若业务只允许从某个负责人向下展开，可以把锚点改成指定员工；若组织数据可能存在环路，则要额外设计去重或路径记录，不能指望叶子条件自动识别环路。

WITH RECURSIVE org_tree AS (
    -- 只从指定负责人开始，避免把其他组织树混进结果
    SELECT employee_id, manager_id, name, 0 AS depth
    FROM employees
    WHERE employee_id = 100

    UNION ALL

    -- 没有下属的员工不会匹配到 child，因此该分支自然结束
    SELECT child.employee_id, child.manager_id, child.name, parent.depth + 1
    FROM employees AS child
    JOIN org_tree AS parent
      ON child.manager_id = parent.employee_id
)
SELECT *
FROM org_tree;

排查时我会先看结果中的 depth 和每个 employee_id 的下属数量：如果叶子节点仍不断产生新行，优先检查 JOIN 是否把错误字段连在一起，而不是先调大服务器递归上限。

用 LIMIT 和递归深度保护异常数据

业务停止条件解决“应该不应该继续”，资源护栏解决“即使数据异常，也不要无限消耗”。MySQL 手册说明，递归成员可以使用 LIMIT 限制递归 CTE 产生的行数；cte_max_recursion_depth 则限制递归层数，超过上限时终止执行。

-- 仅对当前会话设置一个较小的递归层数上限
SET SESSION cte_max_recursion_depth = 50;

WITH RECURSIVE org_tree AS (
    -- 锚点成员：选择组织树根节点
    SELECT employee_id, manager_id, name, 0 AS depth
    FROM employees
    WHERE employee_id = 100

    UNION ALL

    -- LIMIT 是资源护栏，不替代下属关系的停止条件
    SELECT child.employee_id, child.manager_id, child.name, parent.depth + 1
    FROM employees AS child
    JOIN org_tree AS parent
      ON child.manager_id = parent.employee_id
    LIMIT 5000
)
SELECT * FROM org_tree;

这里的数值只是示例，不能直接当作所有组织的推荐配置。组织层级很浅时，较小的深度上限更容易暴露脏数据；层级可能很深时，应先估算合法深度，再设定允许范围。不要为了绕过错误无条件把上限改成很大。

执行超时只做最后一道保护

如果递归查询还可能因为异常关联变慢，可以给当前会话设置 max_execution_time，或者在单条 SELECT 上使用 MAX_EXECUTION_TIME 优化器提示。它们保护的是执行时间，不判断组织树是否正确。

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

WITH RECURSIVE org_tree AS (
    -- 锚点成员：从指定负责人开始
    SELECT employee_id, manager_id, name, 0 AS depth
    FROM employees
    WHERE employee_id = 100

    UNION ALL

    -- 递归成员：沿直属上级字段继续向下查找
    SELECT child.employee_id, child.manager_id, child.name, parent.depth + 1
    FROM employees AS child
    JOIN org_tree AS parent
      ON child.manager_id = parent.employee_id
)
SELECT /*+ MAX_EXECUTION_TIME(1000) */ *
FROM org_tree;

我的取舍是：先确保 JOIN 体现真实的父子关系，再设置适合场景的 LIMIT 和递归深度，最后用执行时间防止异常请求拖住会话。若查询频繁触发护栏，应该回头检查数据是否存在环路、孤儿节点或错误的根节点，而不是把护栏继续放宽。

常见问题

递归 CTE 没有写深度限制一定会死循环吗？

不一定。只要递归成员在叶子节点上不再产生新行，查询可以自然结束；但错误关联、环路或无条件生成新行时就可能持续递归，因此仍应设置资源护栏。

cte_max_recursion_depth 能替代 WHERE 条件吗？

不能。它是服务器对递归层数的保护上限，不表达组织树的业务终点。正确的停止关系仍应写在递归成员的连接或过滤条件中。

小结

组织层级递归的核心是让“没有下属”对应“没有下一轮数据”。LIMIT 控制结果规模，cte_max_recursion_depth 控制递归层数，MAX_EXECUTION_TIME 控制时间消耗；三者配合使用，才能既保留正确的组织关系，又避免异常数据把查询拖入不可控状态。