mysql8.0如何实现公用表表达式CTE_With递归语法实战-mysql教程-PHP中文网

MySQL 8.0.1起支持WITH RECURSIVE，但需满足两个前提：一是cte_max_recursion_depth值不能为0（默认1000），二是语法必须严格为“非递归种子 UNION ALL 递归成员”，且递归引用仅允许在FROM/JOIN中出现。

mysql8.0如何实现公用表表达式cte_with递归语法实战

MySQL 8.0 的 `WITH RECURSIVE` 确实可用，但必须满足两个硬性前提

MySQL 8.0.1 开始正式支持递归 CTE，但不是所有安装都默认启用——关键看是否启用了 cte_max_recursion_depth 系统变量，且该值不能为 0。如果执行递归查询报错 Recursive common table expression is not supported 或直接被拒绝，大概率是这个变量被设成了 0（某些云厂商 MySQL 实例默认禁用）。

检查方式：

SELECT @@cte_max_recursion_depth;

若返回 0，需手动调整（需 SUPER 权限）：

SET GLOBAL cte_max_recursion_depth = 1000;

注意：该值有上限（默认 1000，最大 4294967295），设太高可能触发 ERROR 3636 (HY000): Recursive query aborted after 1000 iterations，此时不是语法错，是深度超限。

递归 CTE 的结构必须严格分两部分：非递归种子 + 递归成员，用 `UNION ALL` 连接

MySQL 不接受 UNION（去重版），只认 UNION ALL；且非递归部分必须在前，递归部分在后，顺序颠倒会报 ERROR 1223 (HY000): Can't execute the given command because you have active locked tables or an active transaction（错误码误导，实际是语法顺序错）。

典型树形结构展开示例（组织架构表 org，含 id, name, parent_id）：

WITH RECURSIVE org_tree AS (
  SELECT id, name, parent_id, 0 AS level
  FROM org
  WHERE parent_id IS NULL  -- 根节点
  UNION ALL
  SELECT o.id, o.name, o.parent_id, ot.level + 1
  FROM org o
  INNER JOIN org_tree ot ON o.parent_id = ot.id
)
SELECT * FROM org_tree ORDER BY level, id;

注意：递归引用 org_tree 必须出现在 FROM 或 JOIN 中，不能出现在 WHERE 子句右侧（如 WHERE id IN (SELECT id FROM org_tree) 会报错）。

MySQL

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

下载

递归查询容易陷入无限循环，必须靠 `level` 或显式终止条件兜底

MySQL 不自动检测环路，若数据存在自引用（比如某条记录 parent_id = id）或双向父子关系，查询会一直跑直到达到 cte_max_recursion_depth 限制才中止，期间 CPU 和内存持续上涨。

防御性写法建议：

始终加入层级计数（如 level 字段），并在递归分支中加 WHERE ot.level 限制深度
对疑似脏数据，提前用 SELECT 检查是否存在 parent_id = id 或 parent_id NOT IN (SELECT id FROM org) AND parent_id IS NOT NULL
避免在递归成员中使用 OR、LIKE、子查询等可能导致索引失效的操作，否则性能断崖式下降

和窗口函数、聚合混用时，递归 CTE 必须作为最外层查询的数据源

你不能在递归 CTE 内部直接写 ROW_NUMBER() OVER() 或 COUNT(*) —— MySQL 会报 ERROR 3575 (HY000): Window function is not allowed in recursive query。正确做法是把递归结果当临时表再加工：

WITH RECURSIVE org_tree AS (
  SELECT id, name, parent_id, 0 AS level
  FROM org WHERE parent_id IS NULL
  UNION ALL
  SELECT o.id, o.name, o.parent_id, ot.level + 1
  FROM org o INNER JOIN org_tree ot ON o.parent_id = ot.id
)
SELECT 
  id, name, parent_id,
  ROW_NUMBER() OVER (PARTITION BY level ORDER BY id) AS seq_in_level,
  COUNT(*) OVER (PARTITION BY level) AS count_in_level
FROM org_tree;

这种写法才能通过解析，也符合执行逻辑：先完整生成递归结果集，再对其做窗口计算。

真正卡住人的往往不是语法，而是递归深度限制、环路数据、以及把聚合/窗口塞进递归体里的惯性思维——这三处出问题，错误信息都长得不像真实原因，得盯住 cte_max_recursion_depth、数据完整性、和 CTE 结构分层这三点才压得住。