SQL存储过程中怎样实现递归逻辑处理树形结构数据？

WITH RECURSIVE是唯一推荐的递归实现方式，因其声明式语法使数据库能自动优化执行计划、天然防死循环、支持深度限制与结果复用；而存储过程手写递归依赖临时表或字符串拼接，易导致索引失效、层级遗漏、死循环及调试困难。

WITH RECURSIVE 是唯一推荐的递归实现方式，存储过程里写循环或拼字符串查树形结构，既难维护又容易出错。

为什么不该在存储过程中手写递归逻辑

数据库原生支持递归 CTE，硬用存储过程模拟只会重复造轮子。常见陷阱包括：

• 用临时表 + WHILE 循环逐层插入，结果漏层级或死循环（比如父子 ID 写反形成环）
• 用 CONCAT 拼接 ID 字符串再 FIND_IN_SET 查询，导致索引失效、无法走 parent_id 索引
• 递归深度不可控，没有 cte_max_recursion_depth 这类机制兜底，一跑就卡住连接
• MySQL 存储过程不支持 RETURNING 或嵌套查询返回结果集，最后还得靠 SELECT 输出，和直接跑 CTE 没区别

必须用 WITH RECURSIVE 而不是存储过程的三个硬性理由

递归 CTE 本身是声明式语法，数据库引擎能自动优化执行计划；而存储过程里的递归是命令式，完全交由用户控制，风险极高：

• WITH RECURSIVE 的终止条件由 JOIN 或 WHERE 隐含约束（如 c.parent_id = t.id），数据库会在每轮迭代后检查是否还能匹配，天然防死循环
• 所有主流支持递归的数据库（MySQL 8.0+、PostgreSQL、SQL Server）都把递归深度限制作为安全边界：cte_max_recursion_depth（MySQL）、statement_timeout 或显式 WHERE level <= 10（PostgreSQL）
• CTE 结果可直接被外层 SELECT、JOIN、甚至视图复用；存储过程输出只能是单结果集或变量，没法当表用

如果真要封装成“可复用逻辑”，该怎么做

不要写存储过程，改用递归视图或带参数的 CTE 查询模板：

• 创建递归视图：CREATE VIEW dept_tree AS WITH RECURSIVE ...，之后任何地方 SELECT * FROM dept_tree WHERE id = ? 即可
• 应用层传参构造 CTE：例如查询某节点所有祖先，用 WHERE id = ? 作锚点，而不是在存储过程中拼 SQL 字符串
• 需要路径拼接？直接在 CTE 里用 CONCAT 或 STRING_AGG（PostgreSQL）生成 /root/child，别在过程里用循环累加
• MySQL 中若需动态起点，可用准备语句（PREPARE + EXECUTE），但仍是 CTE 主体，不是过程逻辑

低版本 MySQL（5.7 及以下）的现实妥协点

没 WITH RECURSIVE 时，存储过程确实成了“次优解”，但必须加三道保险：

• 递归前先查是否存在环：SELECT COUNT(*) FROM categories WHERE id = parent_id，避免自引用死循环
• 手动设最大迭代次数（比如 WHILE i < 20 DO），不能无限制 while true
• 每次循环后用 SELECT ROW_COUNT() 判断是否还有新记录插入，为 0 就 LEAVE
• 即便如此，仍建议优先迁移到 MySQL 8.0+，因为闭包表、路径枚举等替代方案比手写过程更稳定

真正麻烦的从来不是“怎么让 SQL 动起来”，而是“怎么让它停得安全”。CTE 的递归终止是数据库保证的，存储过程里的 IF 判断只是人写的逻辑，差一行就全崩。