MySQL 递归 CTE 怎样设置层级停止条件

MySQL 递归 CTE 的层级停止条件，应该写在递归成员的 WHERE 中：先在锚点成员把根节点的 depth 设为 0，再让递归成员只从尚未到达上限的父节点继续生成子节点。这样“查到几层”是业务规则，cte_max_recursion_depth 和执行超时则只是防止异常数据拖垮查询的保护措施。

MySQL 官方资料：https://dev.mysql.com/doc/refman/8.4/en/with.html

下面用组织树说明。SQL 和结果均为解释写法，不代表本文制作过程中在本机执行过。

先把停止条件写进递归成员

递归 CTE 由两部分组成：第一个 SELECT 产生初始行，第二个 SELECT 引用 CTE 并产生下一批行。真正决定是否继续向下找的，是递归成员里的条件，而不是外层查询的 WHERE。

WITH RECURSIVE org_tree (id, parent_id, name, depth) AS (
    -- 锚点成员：从指定根节点开始，根节点深度记为 0
    SELECT id, parent_id, name, 0
    FROM org_unit
    WHERE id = 100

    UNION ALL

    -- 递归成员：只有父节点还没有达到第 3 层，才生成下一层
    SELECT child.id, child.parent_id, child.name, parent.depth + 1
    FROM org_unit AS child
    JOIN org_tree AS parent ON child.parent_id = parent.id
    WHERE parent.depth 

这个例子中，根节点是第 0 层，parent.depth 允许它生成第 1、2、3 层的子节点；当父节点已经是第 3 层时，递归成员不再为它生成第 4 层。因此条件要看“当前父节点是否还能扩展”，而不是简单地把 child.depth 写成过滤结果。

按业务边界决定什么时候停止

固定层级适合目录、组织架构这类有明确深度预算的查询；如果停止条件来自节点本身，就把它写成递归成员能够判断的业务条件。例如只沿着“仍然开放”的目录向下展开：

WITH RECURSIVE category_tree (id, parent_id, title, depth) AS (
    -- 锚点成员：只选择一个入口目录，并保留入口状态
    SELECT id, parent_id, title, 0
    FROM category
    WHERE id = 10 AND status = 'open'

    UNION ALL

    -- 递归成员：父目录关闭或达到层级上限时，都不再向下展开
    SELECT child.id, child.parent_id, child.title, parent.depth + 1
    FROM category AS child
    JOIN category_tree AS parent ON child.parent_id = parent.id
    WHERE parent.depth 

固定深度和节点状态可以组合，但要明确它们的职责：depth 限制搜索范围，status 表达业务是否允许继续。若查询的是带环的图，而不是保证无环的树，还需要设计访问路径或已访问节点集合；单纯把层数调大，不能解决循环数据。

用执行保护防住异常递归

业务条件之外，MySQL 还提供三类保护。cte_max_recursion_depth 限制递归层数，max_execution_time 或 MAX_EXECUTION_TIME 限制查询时间，递归成员中的 LIMIT 可以限制生成的结果行数。它们用于防止条件写错、数据成环或数据量异常，不应替代业务停止条件。

-- 仅影响当前会话：为组织树设置一个较小的递归深度保险
SET SESSION cte_max_recursion_depth = 20;

WITH RECURSIVE org_tree (id, parent_id, depth) AS (
    -- 锚点成员：根节点作为第 0 层
    SELECT id, parent_id, 0
    FROM org_unit
    WHERE id = 100

    UNION ALL

    -- 递归成员：业务条件仍然负责正常停止
    SELECT child.id, child.parent_id, parent.depth + 1
    FROM org_unit AS child
    JOIN org_tree AS parent ON child.parent_id = parent.id
    WHERE parent.depth 

官方文档说明，默认的 cte_max_recursion_depth 是 1000；具体会话可以按查询需求调整。这里的 LIMIT 是行数保险，提示中的 1000 是毫秒级查询超时示例。生产环境应结合数据规模和调用端超时策略设置，不要把保护值误读为业务允许的最大层级。

写完递归 CTE 后重点复查四个边界

尤其要注意递归列的类型由非递归成员决定。若递归过程中拼接路径，锚点里的字符串类型太短，后续值可能被截断；可以在锚点中用合适的 CAST() 预留宽度。最后用 EXPLAIN 看递归成员的访问方式，但不要把每次迭代成本直接当成整个 CTE 的总成本。

相关问题

把条件写在最外层 SELECT 里能停止递归吗？ 不能。外层条件只过滤已经生成的结果，递归成员仍可能继续产生无用层级；停止条件应放在递归成员中。

cte_max_recursion_depth 能替代业务层级条件吗？ 不能。它是异常保护上限，不表达目录只允许展开几层这样的业务规则。

为什么递归 CTE 明明有 WHERE 还是超出预期？ 先检查锚点深度、父子连接和条件使用的是父行还是子行，再确认是否存在重复父节点或环形数据。