如何实现MySQL 8.0中的递归查询_利用CTE公用表表达式-mysql教程-PHP中文网

MySQL 8.0递归CTE基本写法为：WITH RECURSIVE cte_name AS (初始查询 UNION ALL 递归查询)，其中必须显式使用RECURSIVE关键字，仅允许UNION ALL连接两部分，递归引用须在FROM或JOIN中出现，且顺序不可颠倒。

如何实现mysql 8.0中的递归查询_利用cte公用表表达式

MySQL 8.0 递归 CTE 的基本写法长什么样

MySQL 8.0+ 才支持递归 WITH，语法必须包含 RECURSIVE 关键字，且递归部分只能引用自身一次、必须放在 UNION ALL 右侧。漏掉 RECURSIVE 或写成 UNION 会直接报错 ERROR 1221 (HY000): Incorrect usage of UNION and ORDER BY。

典型结构是：

WITH RECURSIVE t AS (
  SELECT id, name, parent_id, 1 AS level
  FROM categories
  WHERE parent_id IS NULL  -- 初始查询（锚点）
  UNION ALL
  SELECT c.id, c.name, c.parent_id, t.level + 1
  FROM categories c
  INNER JOIN t ON c.parent_id = t.id  -- 递归关联，必须用 INNER JOIN 避免 NULL 匹配
)
SELECT * FROM t;

注意：t 在递归分支里既是被查表，也是正在构建的临时结果集，不能在 WHERE 中对 t 做非关联过滤（比如 t.level 要放到外层 SELECT）。

为什么递归查询总卡死或只返回一层

最常见原因是递归没有终止条件，或连接逻辑错误导致自循环。MySQL 默认递归深度上限是 1000（由系统变量 cte_max_recursion_depth 控制），超限会报错 ERROR 3636 (HY000): Recursive query aborted after 1001 iterations。

检查 ON 条件是否可能让某行反复匹配自己（例如 parent_id = id 写反了）
确保初始查询（锚点）和递归查询的数据域不重叠，否则可能无限生成相同记录
如果业务上树深通常不超过 5 层，可显式限制：SET cte_max_recursion_depth = 10;（会话级生效）
用 level 字段辅助调试：在递归分支中加 t.level + 1，查出来看是否出现重复 level 或突增

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

向下查子节点是自然的父子关联，但向上查父节点容易忽略根节点边界。错误写法是让 parent_id 关联 id 却不限制 parent_id IS NOT NULL，会导致最后一条记录重复自身。

正确模式：

MySQL8 CRM Schema Design Expert

面向 CRM 系统的 MySQL 8 数据库模式设计。当用户需要为客户关系管理（CRM）系统设计、评审、优化或生成数据库模式时，应使用此项技能。

下载

WITH RECURSIVE ancestors AS (
  SELECT id, name, parent_id, 0 AS distance
  FROM categories
  WHERE id = 123  -- 目标叶子节点
  UNION ALL
  SELECT p.id, p.name, p.parent_id, a.distance + 1
  FROM categories p
  INNER JOIN ancestors a ON p.id = a.parent_id
  WHERE p.parent_id IS NOT NULL  -- 关键！终止于根（根的 parent_id 为 NULL）
)
SELECT * FROM ancestors;

这里 WHERE p.parent_id IS NOT NULL 必须写在递归分支内，而不是外层 SELECT —— 否则最后一轮仍会尝试用 NULL 去关联，触发空结果但不报错，逻辑就断了。