mysql8.0执行流中CTE递归查询如何实现_分析递归成员的执行逻辑-mysql教程-PHP中文网

mysql的with recursive按轮次迭代执行，每轮以上轮全部输出为输入驱动join，禁止group by/order by，深度上限由cte_max_recursion_depth控制，且cte必须在join右侧作被驱动表。

mysql8.0执行流中cte递归查询如何实现_分析递归成员的执行逻辑

递归成员不是“循环”，而是迭代追加

MySQL 的 WITH RECURSIVE 并不把递归部分编译成 while 循环或函数调用，而是在执行期按轮次（iteration）生成临时中间结果集，并逐轮追加到 CTE 全局结果中。每一轮的输入是上一轮输出的全部行，不是单行；输出是满足 WHERE 条件的新行集合。

常见误解是“递归查询对每一行单独递归一次”——实际不是。比如锚点返回 3 行，第一轮递归会用这 3 行 *同时* 去 JOIN 表，可能产出 12 行新结果；第二轮再用这 12 行去 JOIN，依此类推。

锚点结果作为第 0 轮输出，存入内部临时表
第 1 轮：用第 0 轮结果驱动 JOIN 和 WHERE，追加匹配行
第 2 轮：用第 1 轮新增行再次驱动，不是用全部历史结果
终止条件是某轮递归查询返回空集（注意：不是“某行不满足 WHERE”，而是整轮无输出）

递归成员里不能用 GROUP BY 或 ORDER BY

因为 MySQL 在设计上禁止在递归分支中做聚合或排序操作——这些操作会破坏“单轮输入 → 单轮输出”的线性迭代模型。一旦你在递归部分写了 GROUP BY，会直接报错：Recursive reference in a subquery is not allowed 或更具体的 Recursive member cannot contain GROUP BY。

如果你需要层级内聚合（比如统计每层子节点数），必须把聚合移到最终 SELECT 中，而不是递归部分里：

WITH RECURSIVE dept_tree AS (
  SELECT id, name, parent_id, 1 AS level
  FROM departments WHERE parent_id IS NULL
  UNION ALL
  SELECT d.id, d.name, d.parent_id, dt.level + 1
  FROM departments d
  INNER JOIN dept_tree dt ON d.parent_id = dt.id
)
SELECT level, COUNT(*) AS node_count
FROM dept_tree
GROUP BY level;  -- ✅ 可以，在最终 SELECT 中

递归成员只允许：SELECT、FROM、JOIN、WHERE、标量表达式（如 level + 1）
禁止：GROUP BY、ORDER BY、LIMIT、DISTINCT、窗口函数、子查询中引用 CTE
如果真要控制每轮数据量，只能靠 WHERE 过滤掉无效路径（比如 level ）

递归深度超限错误的本质是“轮次计数器溢出”

MySQL 默认最多执行 1000 轮迭代（由系统变量 cte_max_recursion_depth 控制），不是“查了 1000 行就停”。当第 1001 轮准备启动但尚未执行时，就会中断并抛出错误：Recursive query aborted after 1000 iterations。

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

这个限制是硬性的、全局的，无法在单条语句里用 OPTION(MAXRECURSION n)（那是 SQL Server 的语法，MySQL 不支持）。

调高限制需改全局或会话级变量：SET SESSION cte_max_recursion_depth = 2000
但盲目调高有风险：若存在环状数据（如 A→B→C→A），只会让崩溃延迟，不会自动检测成环
真正防环得靠业务逻辑：比如记录已访问 id 路径，或用 level 字段显式截断（WHERE level ）
索引缺失时，每轮 JOIN 都可能触发全表扫描，1000 轮 ≈ 扫描 1000×表行数，性能雪崩

递归成员的 JOIN 必须是 INNER JOIN，且 CTE 不能在 RIGHT side

MySQL 强制要求递归部分中对 CTE 的引用只能出现在 FROM 或 JOIN 的左侧（即驱动表位置）。写成 LEFT JOIN dept_tree ON ... 会报错：Recursive reference must be on the right side of JOIN —— 实际上它要求 CTE 必须是被驱动方，也就是放在 JOIN 右侧，且只能是 INNER JOIN。

这是为了保证每轮迭代的数据流方向可控：上轮结果驱动本轮查找，而非反过来。