一、什么是递归查询？
递归查询就像俄罗斯套娃：通过一个查询反复调用自身，逐层解开嵌套的数据结构。
典型应用场景：
二、MySQL 8.0+ 的递归查询语法
基础语法结构
WITH RECURSIVE cte_name AS (
-- 初始查询（锚点）
SELECT ... FROM ...
UNION ALL
-- 递归部分
SELECT ... FROM cte_name, other_tables...
WHERE ...
)
SELECT * FROM cte_name;
三、实战示例
示例1：数字序列生成
-- 生成1到10的数字序列
WITH RECURSIVE number_sequence AS (
SELECT 1 as n -- 初始值
UNION ALL
SELECT n + 1 -- 递归：每次+1
FROM number_sequence
WHERE n < 10 -- 终止条件
)
SELECT * FROM number_sequence;
输出：
n
--
1
2
...
10
示例2：组织架构查询 #数据准备
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
manager_id INT
);
INSERT INTO employees VALUES
(1, 'CEO', NULL),
(2, 'CTO', 1),
(3, '技术总监', 2),
(4, '开发经理', 3),
(5, '开发工程师A', 4),
(6, '开发工程师B', 4),
(7, '产品总监', 2);
#递归查询：找出某员工的所有下属
WITH RECURSIVE employee_tree AS (
-- 初始：从CTO开始
SELECT id, name, manager_id, 0 as level
FROM employees
WHERE name = 'CTO'
UNION ALL
-- 递归：逐级向下查找
SELECT e.id, e.name, e.manager_id, et.level + 1
FROM employees e
INNER JOIN employee_tree et ON e.manager_id = et.id
)
SELECT
LPAD('', level * 4, ' ') || name as 组织架构,
level as 层级
FROM employee_tree;
输出：
组织架构 层级
-
CTO 0
技术总监 1
产品总监 1
开发经理 2
开发工程师A 3
开发工程师B 3
四、分类树查询
数据准备
CREATE TABLE categories (
id INT PRIMARY KEY,
name VARCHAR(50),
parent_id INT
);
INSERT INTO categories VALUES
(1, '电子产品', NULL),
(2, '手机', 1),
(3, '电脑', 1),
(4, '智能手机', 2),
(5, '功能手机', 2),
(6, '笔记本电脑', 3),
(7, '台式机', 3),
(8, '游戏本', 6);
查询完整分类路径
WITH RECURSIVE category_path AS (
SELECT
id,
name,
parent_id,
name as path
FROM categories
WHERE parent_id IS NULL -- 根节点
UNION ALL
SELECT
c.id,
c.name,
c.parent_id,
CONCAT(cp.path, ' > ', c.name)
FROM categories c
INNER JOIN category_path cp ON c.parent_id = cp.id
)
SELECT * FROM category_path;
输出：
id name 父级ID path
-- - -
1 电子产品 NULL 电子产品
2 手机 1 电子产品 > 手机
3 电脑 1 电子产品 > 电脑
4 智能手机 2 电子产品 > 手机 > 智能手机
5 功能手机 2 电子产品 > 手机 > 功能手机
6 笔记本电脑 3 电子产品 > 电脑 > 笔记本电脑
7 台式机 3 电子产品 > 电脑 > 台式机
8 游戏本 6 电子产品 > 电脑 > 笔记本电脑 > 游戏本
五、评论回复嵌套查询
数据准备
CREATE TABLE comments (
id INT PRIMARY KEY,
content TEXT,
parent_comment_id INT,
user_id INT
);
INSERT INTO comments VALUES
(1, '这篇文章很棒！', NULL, 101),
(2, '感谢分享', 1, 102),
(3, '我不同意这个观点', 1, 103),
(4, '能详细解释一下吗？', 3, 104),
(5, '请看参考文献', 4, 103);
查询评论树
WITH RECURSIVE comment_tree AS (
SELECT
id,
content,
parent_comment_id,
user_id,
0 as depth,
CAST(id AS CHAR(100)) as path
FROM comments
WHERE parent_comment_id IS NULL
UNION ALL
SELECT
c.id,
c.content,
c.parent_comment_id,
c.user_id,
ct.depth + 1,
CONCAT(ct.path, '-', c.id)
FROM comments c
INNER JOIN comment_tree ct ON c.parent_comment_id = ct.id
)
SELECT
LPAD('', depth * 4, ' ') || content as 评论内容,
depth as 嵌套深度
FROM comment_tree
ORDER BY path;
六、高级技巧
WITH RECURSIVE limited_tree AS (
SELECT id, name, manager_id, 0 as level
FROM employees
WHERE id = 1
UNION ALL
SELECT e.id, e.name, e.manager_id, lt.level + 1
FROM employees e
INNER JOIN limited_tree lt ON e.manager_id = lt.id
WHERE lt.level < 3 -- 限制最大深度为3层
)
SELECT * FROM limited_tree;
WITH RECURSIVE no_cycle AS (
SELECT
id,
name,
manager_id,
0 as level,
CAST(id AS CHAR(100)) as path
FROM employees
WHERE id = 1
UNION ALL
SELECT
e.id,
e.name,
e.manager_id,
nc.level + 1,
CONCAT(nc.path, '-', e.id)
FROM employees e
INNER JOIN no_cycle nc ON e.manager_id = nc.id
WHERE FIND_IN_SET(e.id, nc.path) = 0 -- 防止循环
)
SELECT * FROM no_cycle;
WITH RECURSIVE level_count AS (
SELECT id, name, manager_id, 0 as level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, lc.level + 1
FROM employees e
INNER JOIN level_count lc ON e.manager_id = lc.id
)
SELECT
level as 层级,
COUNT(*) as 人数
FROM level_count
GROUP BY level
ORDER BY level;
七、性能优化建议
-- 为递归查询的关联字段创建索引
CREATE INDEX idx_manager_id ON employees(manager_id);
CREATE INDEX idx_parent_id ON categories(parent_id);
-- 设置最大递归深度
SET SESSION cte_max_recursion_depth = 1000;
WITH RECURSIVE optimized_tree AS (
SELECT id, name, manager_id, 0 as level
FROM employees e1
WHERE NOT EXISTS (
SELECT 1 FROM employees e2
WHERE e2.manager_id = e1.id
) -- 从叶子节点开始
UNION ALL
SELECT e.id, e.name, e.manager_id, ot.level + 1
FROM employees e
INNER JOIN optimized_tree ot ON e.id = ot.manager_id
)
SELECT * FROM optimized_tree;
八、常见错误与解决
❌ 错误：无限递归
-- 错误示例：缺少终止条件
WITH RECURSIVE infinite AS (
SELECT 1 as n
UNION ALL
SELECT n + 1 FROM infinite -- 没有WHERE条件！
)
SELECT * FROM infinite;
解决： 确保递归部分有明确的终止条件
❌ 错误：循环引用
-- 数据中存在 A→B→A 的循环时
WITH RECURSIVE cycle AS (
SELECT id, name, manager_id
FROM employees
WHERE id = 1
UNION ALL
SELECT e.id, e.name, e.manager_id
FROM employees e
INNER JOIN cycle c ON e.manager_id = c.id
)
SELECT * FROM cycle; -- 可能无限循环
解决： 使用路径追踪防止循环
九、版本兼容性
| MySQL 版本 | 递归查询支持 | ||--| | 5.7 及以下 | ❌ 不支持 | | 8.0+ | ✅ 支持 |
替代方案（5.7及以下）：
path = '1/2/3'）十、实用模板
通用递归查询模板
WITH RECURSIVE custom_tree AS (
-- 初始查询（锚点成员）
SELECT
[columns],
0 as level,
CAST([id_column] AS CHAR(255)) as path
FROM [table_name]
WHERE [root_condition] -- 指定起点
UNION ALL
-- 递归查询（递归成员）
SELECT
t.[columns],
ct.level + 1,
CONCAT(ct.path, '-', t.[id_column])
FROM [table_name] t
INNER JOIN custom_tree ct ON t.[parent_column] = ct.[id_column]
WHERE [termination_condition] -- 终止条件
)
SELECT * FROM custom_tree;
总结
递归查询 = 初始查询 + 递归部分 + 终止条件
适用场景：
记住关键点：
WITH RECURSIVEUNION ALL 连接初始和递归部分通过递归查询，你可以轻松处理复杂的层次数据关系，让数据库帮你完成"一层层剥开"的逻辑！ 另外搭配便捷的80kmMYSQL备份工具，可定时备份、异地备份，MYSQL导出导入。可本地连接LINUX里的MYSQL，简单便捷。可以大大地提高工作效率喔。
你的MySQL中文排序一团糟？记住这几点，轻松实现准确拼音排序！
企业级数据架构：MySQL递归查询在组织权限树中的高级应用实践
企业级MySQL时间管理实践：高并发场景下的性能优化与时区解决方案
【保姆级教程】MySQL主从复制最全配置指南，含监控脚本和故障处理
企业级MySQL高效查询方案：字符串匹配性能优化与全文检索最佳实践
运维基础技能：Linux服务器MySQL版本信息核查的标准化流程
企业级Docker MySQL部署方案：生产环境配置、数据持久化与网络隔离实践
企业级MySQL权限审计指南：从基础查询到安全合规的最佳实践
