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

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

这事儿得先泼盆冷水：如果你手头的MySQL还是5.7或者更老的版本，直接写WITH RECURSIVE语法，铁定会碰一鼻子灰。系统会毫不客气地甩给你一个ERROR 1064 (42000): You ha ve an error in your SQL syntax。别怀疑自己的语法，根本原因是数据库引擎压根不认识这个“新玩意儿”。MySQL直到8.0.1版本才正式把这个功能加进来。

而且，就算版本对了，还有个隐藏关卡：cte_max_recursion_depth这个参数。它默认只允许递归1000层，一旦超了，查询就会戛然而止，并报错ERROR 3636 (HY000): Recursive query aborted after 1000 iterations。

所以，动手前的检查清单必不可少：

SELECT VERSION();

以及

SELECT @@cte_max_recursion_depth;

这里有几个实战要点：

• 版本不对就别硬来。在老版本里，想实现递归效果，通常得靠自连接、存储过程，或者在应用层写循环来模拟。
• 上线部署前，务必双重确认生产环境的MySQL版本和关键配置项的值。
• 如果默认的1000层深度不够用，可以用SET SESSION cte_max_recursion_depth = 3000;临时调高。记住，这个修改只在当前会话生效。

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

这是新手最容易栽跟头的地方。递归CTE的写法有严格的“八股文”格式：必须清晰地分为“锚点部分”和“递归部分”，两者用UNION ALL连接。锚点，也就是第一个SELECT，必须是独立的、非递归的初始数据集，它是整个递归查询的起点。只有在这个起点确立之后，后续的递归成员才能引用CTE自身进行迭代。

举个典型的例子，查询组织架构中某个人的所有下属（包括间接下属）：

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;

写的时候要特别注意这几个坑：

• 锚点部分绝对不能引用递归表的名字（比如上面的org_tree），否则立刻报Unknown table错误。
• 递归成员内部，不允许使用聚合函数、GROUP BY、ORDER BY或LIMIT（这些可以放在最外层查询）。
• JOIN条件写反是致命错误。比如把e.manager_id = ot.id写成ot.manager_id = e.id，结果要么是无限循环，要么返回空集。

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

现实中的数据往往没那么“干净”。想象一下，如果员工A的管理者是B，而B的管理者又被错误地设置成了A，这就形成了一个数据闭环。对于WITH RECURSIVE来说，它可不会智能地识别这个圈套，只会忠实地在A和B之间无限循环下去，直到触发递归深度限制然后报错退出。

遗憾的是，MySQL本身不提供自动的环路检测。所以，这个保险栓必须我们自己来装。一个通用的方法是手动记录访问路径，并判断是否重复进入同一节点。

下面是一个加了防循环机制的安全写法示例（用于查找指定员工的上级链）：

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 = ot.manager_id  -- 注意：这里是向上查上级
  WHERE LOCATE(CONCAT('->', e.id), ot.path) = 0  -- 防止重复进入同一节点
)
SELECT * FROM org_tree;

这个写法的精妙之处在于：

• 新增了一个path字段，像面包屑一样记录遍历过的节点ID序列。
• 在递归成员的WHERE条件中，使用LOCATE函数判断当前节点的ID是否已经在path
• 需要警惕的是，字符串拼接有长度限制，超长会被截断。对于深度极大或ID很长的场景，考虑使用JSON_ARRAY来存储路径，或者将环路检查逻辑移到应用层。
• 关键点：防循环的WHERE条件必须写在递归成员内部。如果只放在最外层，在循环发生时，查询早在递归阶段就已经崩溃了，根本执行不到外层过滤。