如何在MySQL存储过程中优雅实现类似递归查询的逻辑-mysql教程-PHP中文网

mysql 5.7及更早版本存储过程不支持递归call，因引擎层硬性限制；需用临时表+while循环模拟，而8.0+应直接使用with recursive。

如何在mysql存储过程中优雅实现类似递归查询的逻辑

MySQL 5.7 及更早版本的存储过程中，CALL 不能递归调用自身——这是引擎层硬性限制，不是写法问题。所谓“优雅实现”，本质是用循环 + 临时表手动展开递归逻辑，而非真递归。

为什么存储过程里不能直接递归调用自己

执行 CALL get_descendants(1) 再在过程体内写 CALL get_descendants(v_child_id)，会立刻报错：ERROR 1424 (HY000): Recursive stored functions and triggers are not allowed。这不是语法错误，是 MySQL 服务端主动拒绝，连解析阶段都不让过。

原因很实际：老版本缺乏栈帧管理、调用深度控制和嵌套事务隔离支持，放开递归容易导致栈溢出或死锁。

所以别纠结“怎么让 CALL 递归生效”，要转向“怎么用 WHILE + INSERT SELECT 模拟它”。

用临时表 + WHILE 循环模拟后代遍历（含自身）

这是最稳定、可读性最强、也最容易调试的方案，适用于组织架构、分类树等典型父子表。

必须建 TEMPORARY TABLE，不能只靠 @var：用户变量无法承载多行结果，也无法跨循环迭代做集合判断
INSERT INTO ... SELECT 的 WHERE 条件里，一定要加 c.id NOT IN (SELECT id FROM temp_result)，否则父子环或多重路径会导致重复插入、无限循环
每次循环后用 SELECT ROW_COUNT() INTO row_count 判断是否还有新数据；若为 0 就 LEAVE，不能依赖 SELECT COUNT(*)——它可能因并发写入产生误判
临时表建议加 UNIQUE KEY(id)，防住同一节点从不同路径被多次插入

示例关键片段：

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

CREATE PROCEDURE get_descendants(IN root_id INT)
BEGIN
  DECLARE row_count INT DEFAULT 1;
  DROP TEMPORARY TABLE IF EXISTS temp_result;
  CREATE TEMPORARY TABLE temp_result (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    level INT DEFAULT 0
  );
  INSERT INTO temp_result VALUES (root_id, (SELECT name FROM category WHERE id = root_id), 0);
<p>WHILE row_count > 0 DO
INSERT INTO temp_result
SELECT c.id, c.name, t.level + 1
FROM category c
INNER JOIN temp_result t ON c.parent_id = t.id
WHERE c.id NOT IN (SELECT id FROM temp_result);
SELECT ROW_COUNT() INTO row_count;
END WHILE;
END</p>

MySQL 8.0+ 直接用 WITH RECURSIVE，别写存储过程

如果你能升级到 8.0.1+，就彻底放弃“在存储过程中模拟递归”的思路。直接用 WITH RECURSIVE 写成普通查询，性能、可维护性、可测试性全部碾压临时表方案。

例如查某员工所有下属：

WITH RECURSIVE subordinates AS (
  SELECT id, name, manager_id, 1 AS level
  FROM employees WHERE id = 1
  UNION ALL
  SELECT e.id, e.name, e.manager_id, s.level + 1
  FROM employees e
  INNER JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

这个语句可以封装进视图、作为应用层查询直接调用，无需存储过程介入。强行把它塞进 CREATE PROCEDURE 里只是徒增封装层级，没任何收益。