MySQL 8.0+ 应优先使用 WITH RECURSIVE 实现递归查询,避免在存储过程中用 WHILE + 临时表模拟递归;需确保锚点用参数而非变量、parent_id 有索引、JOIN 条件匹配索引顺序,并设置 cte_max_recursion_depth 防止无限递归。

MySQL 8.0+ 直接用 WITH RECURSIVE,别写存储过程
如果你的 MySQL 版本 ≥ 8.0,**在存储过程中硬写递归逻辑是低效且多余的**。原生 WITH RECURSIVE 已能完成所有层级遍历,且执行计划更优、可被索引利用、支持 MAXRECURSION 控制(虽 MySQL 不叫这名字,但可用 cte_max_recursion_depth 系统变量限制)。存储过程里再套循环插入临时表,反而绕过优化器,拖慢查询。
常见错误现象:有人在存储过程中用 WHILE + 临时表模拟递归,结果查询耗时翻倍、并发一高就锁表。原因在于每次循环都是独立 DML,无法复用连接与缓存,还容易漏掉深度 > 5 的分支(因手动设了循环上限)。
- 正确做法:把递归逻辑全写在
SELECT里,存储过程只做参数封装和权限控制 - 必须加
WHERE过滤锚点,避免全表扫描;例如WHERE id = ?而非WHERE parent_id = ?(后者会漏根节点) - 确保
parent_id字段有索引——这是递归 JOIN 能走索引的关键
CREATE PROCEDURE 中调用递归 CTE 的安全写法
存储过程本身不禁止递归 CTE,但要注意作用域和变量注入风险。MySQL 不允许在 CTE 内直接拼接用户传入的 ID 值(如 WHERE id = @input),必须用参数占位符。
示例:安全的员工下属树查询
CREATE PROCEDURE GetSubtree(IN root_id INT)
BEGIN
WITH RECURSIVE org AS (
SELECT employee_id, manager_id, 1 AS depth
FROM Employees
WHERE employee_id = root_id -- 锚点必须用参数值,不能用变量
UNION ALL
SELECT e.employee_id, e.manager_id, o.depth + 1
FROM Employees e
INNER JOIN org o ON e.manager_id = o.employee_id
)
SELECT * FROM org;
END;
关键点:
- 锚点查询必须用
root_id参数,而非会话变量@root_id(后者在 CTE 中不可见) - 递归成员的
JOIN条件必须严格匹配索引字段顺序:e.manager_id = o.employee_id—— 若索引是(manager_id),这个写法才能命中 - 不要在 CTE 外再套一层
SELECT *包裹,MySQL 8.0.20+ 对嵌套 CTE 有额外开销
MySQL 5.7 或更低版本:存储过程里只能靠临时表 + 循环
旧版本没有 WITH RECURSIVE,存储过程是唯一可控方案,但必须规避经典陷阱:无限循环、深度失控、无索引扫描。
核心结构是「临时表 + WHILE + LIMIT 深度」,不是无脑递归:
- 先建带索引的临时表:
CREATE TEMPORARY TABLE tree_result (id INT PRIMARY KEY, depth TINYINT) - 锚点插入后,用
SELECT COUNT(*)判断是否还有新节点可扩展,避免空跑循环 - 每轮循环必须加
LIMIT 1000(或业务允许的最大子节点数),防止某层爆炸式膨胀卡死 - 循环条件里显式检查
depth ,硬性截断,否则遇到循环引用(A→B→C→A)就死锁
性能影响明显:10 层深度可能触发 10 次全表扫描,若没对 manager_id 建索引,单次查询轻松破秒。
递归深度超限或循环引用时,存储过程很难自动发现
这是最容易被忽略的点。MySQL 的 cte_max_recursion_depth 变量只对 CTE 生效,对存储过程里的循环完全无效。你得自己埋检测逻辑。
比如在循环体中加入:
SELECT COUNT(*) INTO @new_count FROM Employees e JOIN temp_tree t ON e.manager_id = t.id WHERE t.depth = @current_depth;
如果 @new_count = 0,说明到底了;但如果某次 @new_count 和上一轮一样,大概率存在循环引用——此时应 LEAVE 并报错,而不是继续循环。
真实业务中,HR 系统偶尔出现「员工 A 的 manager_id 指向自己」或「三人互为上级」,这种脏数据不会触发 SQL 报错,但会让存储过程永远跑不完。必须在代码里主动验重、记路径、设最大跳数。











