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）都把递归深度限制作为安全边界：比如 MySQL 的 cte_max_recursion_depth，PostgreSQL 的 statement_timeout 或显式 WHERE level <= 10。
• 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 及以下）的现实妥协点

如果因为历史原因不得不用低版本 MySQL，没有 WITH RECURSIVE，那存储过程确实成了“次优解”，但至少得加上三道保险：

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

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