MySQL存储过程递归查询多级树状结构实现方法

先给出结论：在 MySQL 8.0 及以上版本中，使用存储过程实现递归查询往往得不偿失。直接采用 WITH RECURSIVE 语法才是更安全、更高效、更易于调试的解决方案。存储过程在处理递归逻辑时容易失控，一旦参数传递错误或索引缺失，轻则无法返回数据，重则触发 ERROR 1104 错误，甚至陷入死循环不断向临时表写入数据——这个陷阱，不少开发者都曾踩过。

MySQL 8.0+ 环境下不应使用存储过程来实现递归查询——直接编写 WITH RECURSIVE 语句更安全、更高效、更便于调试。 存储过程在递归场景中极其容易失控，特别是当参数配置错误或缺少必要索引时，轻则查询不到数据，重则引发 ERROR 1104 (42000) 错误或导致死循环不断向临时表插入数据。

MySQL 8.0+ 推荐使用 WITH RECURSIVE 替代存储过程实现递归查询

不少开发者误以为“必须借助存储过程”才能完成的递归场景，其实只是尚未意识到 WITH RECURSIVE 可以无缝嵌入任意 SQL 上下文——无论是 SELECT、JOIN、视图还是子查询，均可直接使用，完全无需封装成存储过程。

• 查询某节点的所有后代（向下递归）：起始条件设为 WHERE id = ?，JOIN 条件写为 c.parent_id = ct.id
• 查询某节点的所有祖先（向上递归）：起始条件同样为 WHERE id = ?，但 JOIN 条件需反向书写为 c.id = a.parent_id
• 字段类型必须显式保持一致：例如 parent_id 为 INT 类型，就不能与 id 类型不匹配的字段进行 JOIN，否则隐式类型转换会导致查询失败
• 默认递归深度上限为 1000 层，若树结构深度超过此值，需提前执行 SET SESSION cte_max_recursion_depth = 3000 进行调整

以下示例演示了如何查询 ID 为 123 的所有祖先节点：

WITH RECURSIVE ancestors AS (
  SELECT id, name, parent_id, 0 AS depth
  FROM categories
  WHERE id = 123
  UNION ALL
  SELECT c.id, c.name, c.parent_id, a.depth + 1
  FROM categories c
  INNER JOIN ancestors a ON c.id = a.parent_id
  WHERE c.parent_id IS NOT NULL  -- 防止根节点后继续递归出空行
)
SELECT * FROM ancestors ORDER BY depth DESC;

MySQL 5.7 及更早版本才需要考虑存储过程方案

如果你的 MySQL 版本较低，不支持 WITH RECURSIVE 语法，也不要急于编写存储过程——先确认业务是否真的需要“动态未知深度”的递归查询。许多场景只需查询 2 到 3 层，使用 LEFT JOIN 连续关联 3 次表，比存储过程执行更快、更可控，也更容易利用索引进行优化。

• 只有当业务明确要求“从叶子节点向上无限回溯”或“展开全部后代且深度不可预知”时，才考虑采用存储过程方案
• 必须创建临时表，并至少对 id 和 parent_id 字段建立联合索引，否则后续的 INSERT ... SELECT WHERE parent_id IN (...) 操作将触发全表扫描
• 退出循环必须依赖 ROW_COUNT() = 0 进行判断，不能仅靠 WHILE done = FALSE——后者在无数据时不会自动将 done 置为真
• 传入 NULL 或不存在的 id 会导致存储过程静默返回空结果，建议在过程开头添加 IF NOT EXISTS(SELECT 1 FROM categories WHERE id = in_id) THEN LEAVE proc_label; END IF; 进行保护性校验

存储过程中最容易踩的三个坑

即使你确认必须使用存储过程，以下三点若不处理，上线后极大概率会出现故障：

• TEMPORARY TABLE 未设置主键或唯一索引：导致数据重复插入，INSERT ... SELECT 性能急剧下降
• 递归插入时遗漏 WHERE parent_id IN (SELECT id FROM temp_table) 中的括号，或错误地写成 =，导致仅插入一层数据
• 调用存储过程前未设置 max_sp_recursion_depth（默认值为 0，即禁用递归），导致过程直接报错 ERROR 1422 (HY000): Explicit or implicit commit is not allowed in stored function or trigger

真正的难点从来不是“写出来”，而是让递归逻辑在各种边界输入（空树、单节点、环形引用）下不崩溃、不卡死、不返回脏数据。这需要大量测试用例进行覆盖，其成本远超一条 WITH RECURSIVE 语句。因此，能用 CTE 就优先用 CTE，不要再与存储过程较劲了。