MySQL 8.0中公用表表达式（CTE）如何解决递归查询问题？-mysql教程-PHP中文网

MySQL 8.0递归查询必须显式使用WITH RECURSIVE，因解析器强制校验自引用；漏写RECURSIVE、用UNION替代UNION ALL或CTE嵌套子查询均触发语法硬性报错，非配置问题。

mysql 8.0中公用表表达式（cte）如何解决递归查询问题？

MySQL 8.0 的递归查询只能靠 WITH RECURSIVE 实现，其他写法（比如漏掉 RECURSIVE、用 UNION 替代 UNION ALL、在子查询里嵌套 CTE）一律报错，不是配置问题，是语法硬性拒绝。

为什么 `WITH cte AS ()` 会报 “Recursive reference not allowed”？

MySQL 8.0+ 要求：只要 CTE 里出现自引用（即在递归部分写了 FROM cte 或 JOIN cte），就必须显式写成 WITH RECURSIVE cte AS ()。这不是可选项，是解析器第一道校验。

常见错误现象：

复制了别人代码但删掉了 RECURSIVE 关键字 → 报错 ERROR 1223 (HY000): Can't execute the query because it contains recursion
写了 WITH RECURSIVE，但锚点和递归部分用了 UNION → 报错 ERROR 1221 (HY000): Incorrect usage of UNION and ORDER BY
把 CTE 写在子查询里，例如 SELECT * FROM (WITH t AS ... SELECT ...) s → 报错 ERROR 1064 (42000)，因为 CTE 必须是语句最外层前缀

`WITH RECURSIVE` 执行时总卡住或只返回一层？

本质不是“慢”，而是迭代轮次没触发终止，或某轮 JOIN 没产出新行。MySQL 是按轮次（iteration）执行的：第 0 轮跑锚点，第 1 轮用第 0 轮结果驱动 JOIN，第 2 轮用第 1 轮新增行再驱动……直到某轮递归查询返回空集才停。

容易踩的坑：

kashen-mysql DDLtoExcel

解析 MySQL 8.0 的 DDL 脚本，并将表结构导出为 Excel 格式。适用于用户需将 MySQL 的 `CREATE TABLE` 语句转换为结构化的 Excel 文件场景，导出内容包含字段名、数据类型、长度及精度等信息。支持多张表的批量处理，每张表对应 Excel 中的一个独立工作表。适用于数据库文档编写、数据字典生成及模式分析等任务。

下载

ON 条件写反，比如 ON c.id = t.parent_id（应为 c.parent_id = t.id），导致自循环，每轮都匹配自己
没加 WHERE 终止条件，或条件太宽松（如只写 WHERE parent_id IS NOT NULL），但数据里存在 parent_id = id 的脏数据
递归部分用了 LEFT JOIN 或把 CTE 放在 JOIN 左侧 → 报错 ERROR 3641 (HY000): Recursive reference to CTE 'xxx' is not allowed in this context
忘了建索引：连接字段（如 parent_id）没索引，每轮都是全表扫描，深树直接拖垮

如何安全查某个节点的所有祖先（向上递归）？

向下查子节点是自然父子关系，但向上查父节点容易漏掉根边界，导致最后一层重复自身或无限回溯。

正确写法要点：

锚点必须是目标节点本身，例如 WHERE id = 123，不是 WHERE parent_id = 123
递归部分的 JOIN 条件是 ON d.id = p.parent_id（基础表 d 关联 CTE p 的 parent_id），不是反过来
必须加 WHERE p.parent_id IS NOT NULL，否则当某行 parent_id 为 NULL 时，下一轮仍会拿这行去 JOIN，造成无效循环
建议加 level 字段并限制深度，例如 WHERE p.parent_id IS NOT NULL AND p.level ，避免意外超限

示例片段：

WITH RECURSIVE ancestors AS (
  SELECT id, name, parent_id, 1 AS level
  FROM categories
  WHERE id = 123
  UNION ALL
  SELECT c.id, c.name, c.parent_id, a.level + 1
  FROM categories c
  INNER JOIN ancestors a ON c.id = a.parent_id
  WHERE a.parent_id IS NOT NULL AND a.level <p>真正难的不是写对语法，而是验证数据里有没有环——比如 A→B→C→A 这种隐式闭环，<code>level</code> 和路径字符串（<code>CONCAT(path, '/', id)</code> + <code>NOT LIKE</code>）得配合用，光靠 <code>cte_max_recursion_depth</code> 只是兜底，不是解法。</p>