如何使用MySQL 8.0递归CTE查询层级数据-mysql教程-PHP中文网

mysql 8.0递归cte基本语法必须包含锚成员和递归成员两部分，用union all连接，且需显式声明recursive关键字；锚成员独立执行不引用cte自身，递归成员通过join引用cte并依赖终止条件（如level限制或where子句）防止无限循环。

如何使用mysql 8.0递归cte查询层级数据

MySQL 8.0递归CTE的基本语法结构

MySQL 8.0+ 支持标准 SQL 的递归 WITH RECURSIVE，但必须严格满足两个部分：非递归种子查询（anchor）和递归查询（recursive term），且二者用 UNION ALL 连接。漏掉 RECURSIVE 关键字、用 UNION 替代 UNION ALL，或递归引用自身时没加别名，都会报错 ERROR 1221 (HY000): Incorrect usage of UNION and ORDER BY 或直接拒绝执行。

典型结构如下：

WITH RECURSIVE tree AS (
  -- 种子：查顶层节点（例如 parent_id IS NULL）
  SELECT id, name, parent_id, 0 AS level
  FROM categories
  WHERE parent_id IS NULL
<p>UNION ALL</p><div class="aritcle_card flexRow artxards">
											<div class="artcardd flexRow">
												<a class="aritcle_card_img" rel="nofollow" href="/xiazai/skill2334" title="MySQL"><img
														src="https://img.php.cn/upload/skill/000/000/081/178900927846657.jpg" alt="MySQL" onerror="this.onerror='';this.src='/static/lhimages/moren/morentu.png'" ></a>
												<div class="aritcle_card_info flexColumn">
													<a rel="nofollow" href="/xiazai/skill2334" title="MySQL" class="overflowclass">MySQL</a>
													<p class="overflowclass">编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。</p>
												</div>
												<a rel="nofollow" href="/xiazai/skill2334" title="MySQL" class="aritcle_card_btn flexRow flexcenter"><b></b><span>下载</span>
												</a>
											</div>
										</div><p>-- 递归：关联上一层的 id 和下一层的 parent_id
SELECT c.id, c.name, c.parent_id, t.level + 1
FROM categories c
INNER JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree ORDER BY level, id;</p>

种子查询必须能独立执行，不能依赖递归表别名 tree
递归分支中，JOIN 条件必须是「子表外键 = 父表主键」方向，反写会导致无限循环或空结果
level 字段不是必需，但强烈建议加上，便于控制深度和排序

如何防止无限递归和栈溢出

MySQL 默认限制递归深度为 100 层（由系统变量 cte_max_recursion_depth 控制），超出就中断并报错 ERROR 3636 (HY000): Recursive query aborted after 100 iterations。这不一定是数据有环，更可能是设计时没预判层级深度。

安全做法是主动设限：

SET SESSION cte_max_recursion_depth = 50;

或者在 CTE 内部用 level 截断：

WITH RECURSIVE tree AS (
  SELECT id, name, parent_id, 0 AS level
  FROM categories
  WHERE parent_id IS NULL
<p>UNION ALL</p><p>SELECT c.id, c.name, c.parent_id, t.level + 1
FROM categories c
INNER JOIN tree t ON c.parent_id = t.id
WHERE t.level </p>

永远不要依赖默认 100 层 —— 生产环境应显式设置 cte_max_recursion_depth，避免突发超深树拖垮连接
用 WHERE t.level 比全局设限更精准，尤其当不同业务路径深度差异大时
如果怀疑数据存在闭环（比如 A→B→C→A），需先用非递归方式检测：查 parent_id 是否指向本记录或祖先

递归结果排序与路径构建技巧

直接 ORDER BY level 只能做层内粗排，无法实现树形展示（如缩进、同父节点连续）。真实需求常要生成路径字符串或排序序号。

用 CONCAT 和 LPAD 构建可读路径：

WITH RECURSIVE tree AS (
  SELECT id, name, parent_id, 0 AS level, CAST(name AS CHAR(500)) AS path
  FROM categories
  WHERE parent_id IS NULL
<p>UNION ALL</p><p>SELECT c.id, c.name, c.parent_id, t.level + 1,
CONCAT(t.path, ' → ', c.name)
FROM categories c
INNER JOIN tree t ON c.parent_id = t.id
)
SELECT id, LPAD('', level * 2, ' ') || name AS indented_name, path
FROM tree
ORDER BY path;</p>

path 字段类型需显式转为 CHAR 或 VARCHAR，否则 MySQL 可能推导为过短长度，导致截断
用 ORDER BY path 能天然实现字典序树形遍历，比靠 level + id 排更可靠
LPAD('', level * 2, ' ') 比拼接多个空格更可控；注意 MySQL 8.0+ 支持 || 字符串拼接（需开启 PIPES_AS_CONCAT 模式，或直接用 CONCAT()）