如何在MySQL中通过存储过程实现多级分类的递归查询？-mysql教程-PHP中文网

MySQL 8.0+ 应优先使用 WITH RECURSIVE 实现递归查询，简洁安全易维护；5.7 及更早版本仅在确需动态深度时才考虑存储过程，否则推荐多表 JOIN 或应用层处理。

如何在mysql中通过存储过程实现多级分类的递归查询？

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

MySQL 5.7 及更早版本不支持递归 CTE，但如果你用的是 MySQL 8.0 或更高版本，WITH RECURSIVE 是最简、最安全、最易维护的方案——存储过程反而会引入额外复杂度和调试成本。

常见错误是强行在 8.0+ 环境里封装成存储过程，结果：参数传错导致死循环、没加 MAX_RECURSION_DEPTH 限制触发 ERROR 1104 (42000): The SELECT would examine more than MAX_JOIN_SIZE rows、无法直接用在视图或 JOIN 中。

正确做法是把递归逻辑写成可复用的 CTE 查询：

WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, 1 AS level
  FROM categories
  WHERE id = 123  -- 起始分类 ID
  UNION ALL
  SELECT c.id, c.name, c.parent_id, ct.level + 1
  FROM categories c
  INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY level;

MySQL 5.7 必须用存储过程？先确认是否真需要「动态深度」

很多场景其实只需要查固定层级（比如“查某分类及其直接子类、孙类”），这时用 LEFT JOIN 连 3 次表比存储过程更稳、更快、更容易索引优化。

只有当业务明确要求“任意深度向上找祖先”或“向下展开全部后代且深度不可预知”时，才考虑存储过程。否则容易踩坑：

存储过程里用临时表 + 循环插入，没加 PRIMARY KEY 或 INDEX 导致后续查询极慢
忘记用 ROW_COUNT() 判断循环退出条件，造成无限插入
调用时传入非法 id（如 NULL 或不存在值），过程静默失败但返回空结果

若确认必须用，核心结构应为：建临时表 → 插入起始节点 → 循环插入子节点（WHERE parent_id IN (已存ID)）→ 用 ROW_COUNT() = 0 退出。

MySQL

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

下载

`CREATE PROCEDURE` 中递归的关键控制点

MySQL 存储过程没有原生递归函数，所谓“递归”本质是迭代模拟。必须手动控制边界和终止条件：

临时表字段至少包含 id、parent_id、level，level 用于防过深（比如 >10 层就 LEAVE）
每次循环前用 SELECT COUNT(*) INTO @cnt 查待处理节点数，@cnt = 0 就 LEAVE
避免用 INSERT ... SELECT 无限制插入，务必加 LIMIT 1000 防爆内存（尤其数据量大时）
调用前设 SET max_sp_recursion_depth = 255（默认是 0，禁用嵌套调用）

示例片段（向下查后代）：

CREATE PROCEDURE GetSubCategories(IN root_id INT)
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DROP TEMPORARY TABLE IF EXISTS temp_tree;
  CREATE TEMPORARY TABLE temp_tree (
    id INT PRIMARY KEY,
    level TINYINT DEFAULT 0
  );
  INSERT INTO temp_tree VALUES (root_id, 0);
<p>REPEAT
INSERT IGNORE INTO temp_tree
SELECT c.id, t.level + 1
FROM categories c
INNER JOIN temp_tree t ON c.parent_id = t.id
WHERE t.level </p><p>SELECT * FROM temp_tree;
END</p>