如何通过MySQL存储过程实现多级树状结构的递归查询逻辑？

MySQL 8.0+ 不该用存储过程实现递归查询，应直接使用 WITH RECURSIVE——因其更安全、高效、易调试，且能嵌入任意 SQL 上下文；存储过程易因参数错误或索引缺失导致查不出数据、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 及更早版本才考虑存储过程

低版本不支持 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 的成本。