MySQL高级讲座篇之：MySQL递归查询：在层级数据结构中实现复杂查询。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

各位观众老爷，大家好！今天咱们聊点高级的——MySQL 递归查询，保证让你的数据库功力更上一层楼。

开场白：别怕递归，它其实很友好

一提到“递归”，很多人脑子里可能马上冒出“无限循环”、“栈溢出”这些恐怖字眼。别慌！其实递归并没有那么可怕，它只是解决问题的一种巧妙方式，特别适合处理具有层级结构的数据。想象一下，你家公司的组织架构，或者电商网站的商品分类，是不是一层套一层？这种数据结构用递归查询来处理，简直是事半功倍。

一、什么是递归查询？

简单来说，递归查询就是在一个查询语句中，调用自身来获取层级关系的数据。就像查族谱一样，先找到你，再找到你爸，再找到你爷爷，一直往上追溯。

在MySQL 8.0之前，实现递归查询是一件比较麻烦的事情，通常需要用到存储过程或者自定义函数。但自从有了 Common Table Expression (CTE)，也就是“通用表达式”，递归查询变得简单多了。

二、 CTE：递归查询的利器

CTE 就像一个临时的查询结果集，你可以在一个查询语句中定义它，并在后续的查询中引用它。 CTE 分为两种：普通 CTE 和递归 CTE。咱们这里主要讲递归 CTE。

递归 CTE 的基本结构如下：

WITH RECURSIVE cte_name AS (
    -- 初始查询 (Anchor Member)
    SELECT ...
    UNION ALL
    -- 递归查询 (Recursive Member)
    SELECT ... FROM cte_name WHERE ...
)
-- 主查询
SELECT * FROM cte_name;

WITH RECURSIVE cte_name AS (…): 声明一个递归 CTE，并给它起个名字（例如 cte_name）。 RECURSIVE 关键字是必须的，告诉 MySQL 这是一个递归 CTE。
初始查询 (Anchor Member): 这是递归的起点，它返回递归的第一层数据。就像族谱里的“你”一样。
UNION ALL: 将初始查询和递归查询的结果合并起来。 UNION ALL 比 UNION 效率更高，因为它不会去重。
递归查询 (Recursive Member): 这是递归的核心，它会引用 CTE 自身，并根据一定的条件，查询下一层的数据。就像从“你”找到“你爸”一样。 务必小心，一定要有终止条件，否则就会陷入无限循环！
主查询: 从 CTE 中查询最终结果。

三、实战演练：商品分类的递归查询

咱们用一个电商网站的商品分类表来举例说明。假设我们有这样一张表 category：

category_id	parent_id	category_name
1	0	电子产品
2	0	服装鞋帽
3	1	智能手机
4	1	笔记本电脑
5	2	男装
6	2	女装
7	3	iPhone
8	3	Android 手机
9	5	T恤
10	5	衬衫

category_id: 分类 ID (主键)
parent_id: 父分类 ID (0 表示顶级分类)
category_name: 分类名称

现在，我们想查询某个分类的所有子分类，包括它的子分类的子分类，以此类推。比如，查询 电子产品 (category_id = 1) 的所有子分类。

WITH RECURSIVE sub_categories AS (
    -- 初始查询：找到 category_id = 1 的分类
    SELECT category_id, parent_id, category_name
    FROM category
    WHERE category_id = 1
    UNION ALL
    -- 递归查询：找到 parent_id 等于上一次查询的 category_id 的分类
    SELECT c.category_id, c.parent_id, c.category_name
    FROM category c
    INNER JOIN sub_categories sc ON c.parent_id = sc.category_id
)
-- 主查询：从 sub_categories 中查询所有结果
SELECT * FROM sub_categories;

这个查询的执行流程是这样的：

初始查询: 找到 category_id = 1 的分类，也就是 电子产品。
递归查询: 找到 parent_id 等于 1 的分类，也就是 智能手机 和 笔记本电脑。
递归查询 (继续): 找到 parent_id 等于 3 (智能手机的 category_id) 的分类，也就是 iPhone 和 Android 手机。找到 parent_id 等于 4 (笔记本电脑的 category_id) 的分类，这里没有符合条件的，递归结束。
主查询: 将所有查询结果合并起来，得到 电子产品 及其所有子分类。

查询结果：

category_id	parent_id	category_name
1	0	电子产品
3	1	智能手机
4	1	笔记本电脑
7	3	iPhone
8	3	Android 手机

四、进阶技巧：控制递归深度

有时候，我们只想查询特定深度的子分类。比如，只想查询 电子产品 的一级子分类（也就是 智能手机 和 笔记本电脑），不想查询更深的层级。

我们可以通过在递归 CTE 中添加一个 level 字段来控制递归深度。

WITH RECURSIVE sub_categories AS (
    -- 初始查询：level = 1
    SELECT category_id, parent_id, category_name, 1 AS level
    FROM category
    WHERE category_id = 1
    UNION ALL
    -- 递归查询：level + 1，并限制 level <= 2
    SELECT c.category_id, c.parent_id, c.category_name, sc.level + 1 AS level
    FROM category c
    INNER JOIN sub_categories sc ON c.parent_id = sc.category_id
    WHERE sc.level < 2 -- 限制递归深度
)
-- 主查询：从 sub_categories 中查询所有结果
SELECT * FROM sub_categories;

在这个查询中，我们在初始查询中添加了一个 level 字段，并将其初始值设为 1。在递归查询中，我们将 level 字段的值加 1，并添加一个 WHERE sc.level < 2 的条件，限制递归深度为 2。

查询结果：

category_id	parent_id	category_name	level
1	0	电子产品	1
3	1	智能手机	2
4	1	笔记本电脑	2

五、更多应用场景

除了商品分类，递归查询还可以应用于很多其他场景：

组织架构: 查询某个员工的所有下属，或者某个部门的所有子部门。
评论回复: 查询某个评论的所有回复，以及回复的回复，以此类推。
文件目录: 查询某个目录下的所有文件和子目录。
族谱关系: 查询某个人的所有祖先或后代。

六、注意事项

终止条件: 一定要在递归查询中设置终止条件，否则会陷入无限循环，导致数据库崩溃。
性能: 递归查询可能会比较耗时，特别是当数据层级很深的时候。尽量优化查询语句，避免不必要的计算。
MySQL 版本: 递归 CTE 是 MySQL 8.0 及以上版本才支持的特性。如果你的 MySQL 版本比较低，需要使用其他方法来实现递归查询（例如存储过程或自定义函数）。
循环依赖: 避免出现循环依赖的情况，例如 A 是 B 的父类，B 又是 A 的父类。这种情况会导致无限循环。

七、总结

递归查询是一种强大的工具，可以帮助我们处理具有层级结构的数据。通过使用 CTE，我们可以轻松地实现递归查询，并从中获取所需的信息。掌握递归查询，可以让你在数据库操作中更加得心应手。

八、附加案例：查找所有父级分类

与之前的查找子分类相反，我们现在要查找指定分类的所有父级分类，直到顶级分类为止。

WITH RECURSIVE parent_categories AS (
    -- 初始查询：找到 category_id = 7 的分类（例如 iPhone）
    SELECT category_id, parent_id, category_name
    FROM category
    WHERE category_id = 7
    UNION ALL
    -- 递归查询：找到 category 表中 category_id 等于上一次查询的 parent_id 的分类
    SELECT c.category_id, c.parent_id, c.category_name
    FROM category c
    INNER JOIN parent_categories pc ON c.category_id = pc.parent_id
)
-- 主查询：从 parent_categories 中查询所有结果
SELECT * FROM parent_categories;

这个查询的逻辑是：

初始查询: 找到 category_id = 7 的分类，即 "iPhone"。
递归查询: 找到 category 表中 category_id 等于 "iPhone" 的 parent_id（即 3）的分类，即 "智能手机"。
递归查询 (继续): 找到 category 表中 category_id 等于 "智能手机" 的 parent_id（即 1）的分类，即 "电子产品"。
递归查询 (继续): 找到 category 表中 category_id 等于 "电子产品" 的 parent_id（即 0）的分类，没有找到，递归结束。
主查询: 将所有结果合并，得到 "iPhone" 的所有父级分类。

查询结果：

category_id	parent_id	category_name
7	3	iPhone
3	1	智能手机
1	0	电子产品

九、性能优化建议

递归查询在处理大量数据时可能会遇到性能瓶颈，以下是一些优化建议：

索引优化: 确保参与递归查询的字段（例如 category_id 和 parent_id）都有索引。索引可以显著提高查询速度。
限制递归深度: 如果知道数据的最大深度，可以使用 level 字段来限制递归深度，避免不必要的查询。
数据量控制: 尽量减少参与递归查询的数据量。例如，可以先通过其他条件过滤数据，然后再进行递归查询。
物化 CTE: 在某些情况下，可以将 CTE 的结果物化（即将其保存到临时表中），然后再进行后续查询。这可以避免重复计算，提高查询效率。 MySQL 8.0.19 引入了 MATERIALIZED 提示，可以强制 MySQL 物化 CTE。例如：

WITH RECURSIVE sub_categories AS MATERIALIZED (
    -- ... (递归查询) ...
)
-- 主查询
SELECT * FROM sub_categories;

避免循环依赖: 确保数据中没有循环依赖的情况，否则会导致无限循环，严重影响性能。
硬件升级: 如果以上优化都无法满足需求，可以考虑升级数据库服务器的硬件配置，例如增加内存、CPU 或使用 SSD 硬盘。

十、错误处理和调试

递归查询出错时，通常会报 "Error 3639: Recursive query max depth exceeded. Increase @@cte_max_recursion_depth to a larger value." 错误。这意味着递归深度超过了 MySQL 的默认限制。

可以通过以下方法解决：

增加 @@cte_max_recursion_depth 的值: 可以使用以下命令修改 cte_max_recursion_depth 的值：

SET GLOBAL cte_max_recursion_depth = 1000; -- 设置为 1000，根据实际情况调整

注意：GLOBAL 范围的设置会影响所有新的连接。如果只想修改当前会话的设置，可以使用 SESSION 范围：

SET SESSION cte_max_recursion_depth = 1000;

务必谨慎调整这个值，过大的值可能会导致性能问题。

检查终止条件: 仔细检查递归查询的终止条件是否正确。如果终止条件不正确，递归查询可能会陷入无限循环。
调试技巧: 可以使用 EXPLAIN 命令分析查询计划，查看 MySQL 如何执行递归查询。也可以在递归查询中添加 level 字段，并打印 level 的值，以便了解递归的深度和执行过程。

十一、总结的总结

好了，今天的 MySQL 递归查询讲座就到这里了。希望通过今天的讲解，大家对递归查询有了更深入的了解。记住，递归并没有那么可怕，只要掌握了基本原理和注意事项，就可以用它来解决很多实际问题。以后在工作中遇到层级数据结构，不妨试试递归查询，相信它会给你带来惊喜。

如果大家还有什么疑问，欢迎随时提问。感谢大家的收看，我们下期再见！

发表回复 取消回复

发表回复取消回复