SQL怎样在MySQL中实现递归查询_使用WITH RECURSIVE公用表-SQL-PHP中文网

mysql 8.0.1+才支持with recursive，5.7及更早版本直接报语法错误；需检查version()和cte_max_recursion_depth；锚点与递归成员须分离，且须防数据环路。

sql怎样在mysql中实现递归查询_使用with recursive公用表

MySQL 8.0+ 才支持 `WITH RECURSIVE`，低版本直接报错

如果你在 MySQL 5.7 或更早版本里写 WITH RECURSIVE，会收到类似 ERROR 1064 (42000): You have an error in your SQL syntax 的错误——不是语法写错了，是压根不支持。MySQL 直到 8.0.1 版本才正式加入对递归 CTE 的支持，且必须开启 cte_max_recursion_depth（默认 1000），超深层级会中断并报 ERROR 3636 (HY000): Recursive query aborted after 1000 iterations。

检查方法：

SELECT VERSION();

和

SELECT @@cte_max_recursion_depth;

升级前别硬套语法，老版本得用自连接、存储过程或应用层迭代模拟
上线前务必确认目标环境 MySQL 版本和配置项值
递归深度不够时，用 SET SESSION cte_max_recursion_depth = 3000; 临时调高（注意会话级生效）

`WITH RECURSIVE` 的锚点与递归成员必须严格分离

常见错误是把初始查询（anchor member）和递归查询（recursive member）混写成一个 UNION ALL 左右都带条件，或者漏掉锚点——MySQL 要求第一个 SELECT 必须是非递归的“起点”，后续才允许引用自身。

比如查组织架构树中某人所有下属（含间接）：

MySQL

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

下载

WITH RECURSIVE org_tree AS (
  -- 锚点：从 CEO 开始（manager_id IS NULL）
  SELECT id, name, manager_id, 0 AS level
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  -- 递归成员：必须引用 org_tree，且 JOIN 条件要能收敛（通常靠 manager_id = org_tree.id）
  SELECT e.id, e.name, e.manager_id, ot.level + 1
  FROM employees e
  INNER JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree;

锚点不能依赖递归表名，否则报 Unknown table 'org_tree' in field list
递归成员里不能出现聚合、GROUP BY、ORDER BY、LIMIT（除最外层）
JOIN 条件写反（如 ot.manager_id = e.id）会导致无限循环或空结果

递归查询容易因数据环路崩溃，必须加防循环机制

如果员工 A 管理 B，B 又被设为 A 的上级（数据脏），WITH RECURSIVE 会一直绕圈直到触达 cte_max_recursion_depth，然后报错退出。MySQL 不自动检测环路，得手动加路径记录和重复判断。

安全写法示例（记录已遍历 ID 路径）：

WITH RECURSIVE org_tree AS (
  SELECT id, name, manager_id, 0 AS level, CAST(id AS CHAR(1000)) AS path
  FROM employees WHERE id = 123  -- 从指定员工开始查其上级链
  UNION ALL
  SELECT e.id, e.name, e.manager_id, ot.level + 1,
         CONCAT(ot.path, '->', e.id)
  FROM employees e
  INNER JOIN org_tree ot ON e.id = e.manager_id  -- 注意：这里是向上查上级
  WHERE LOCATE(CONCAT('->', e.id), ot.path) = 0  -- 防止重复进入同一节点
)
SELECT * FROM org_tree;