MySQL CTE 实现多级嵌套数据分层结构查询
大家好，今天我们来深入探讨如何利用 MySQL 的 CTE (Common Table Expression) 实现复杂的多级嵌套数据分层结构查询。分层数据结构在很多领域都有应用，比如组织架构、产品分类、地理位置等等。传统的 SQL 查询处理这种结构往往比较复杂，而 CTE 提供的递归功能可以简化这类查询，使代码更易读、更易维护。
一、分层数据结构及其存储
首先，我们需要明确什么是分层数据结构以及如何在数据库中存储它。分层数据结构，也称为树状结构，由节点和边组成。每个节点可以有多个子节点，但只有一个父节点（根节点没有父节点）。
在数据库中，我们通常使用邻接表模型来存储分层数据。这种模型使用一个表，其中包含每个节点的 ID、父节点 ID 和其他相关信息。
例如，我们有一个categories表，用于存储商品分类信息：
CREATE TABLE `categories` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`parent_id` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `parent_id` (`parent_id`),
CONSTRAINT `categories_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `categories` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `categories` (`name`, `parent_id`) VALUES
('电子产品', NULL),
('电脑', 1),
('手机', 1),
('笔记本电脑', 2),
('台式机', 2),
('智能手机', 3),
('功能手机', 3),
('配件', 1),
('键盘', 8),
('鼠标', 8);在这个表中，id是分类的唯一标识符，name是分类名称，parent_id是父分类的 ID。parent_id为 NULL 表示该分类是根分类。
二、CTE 递归查询
CTE 是一种临时命名的结果集，可以在单个查询中引用。它类似于子查询，但更易于阅读和维护。更重要的是，CTE 可以通过递归来处理分层数据。
递归 CTE 由两部分组成：
- 锚定成员 (Anchor Member): 这是 CTE 的基础情况，它选择递归的起始行。它必须返回与递归成员相同列数的列。
- 递归成员 (Recursive Member): 这是 CTE 的递归部分，它引用 CTE 自身来迭代地选择相关行。它必须使用
UNION ALL或UNION DISTINCT与锚定成员连接。
2.1 查询所有子节点
假设我们要查询某个分类的所有子节点，包括直接子节点和间接子节点。例如，我们要查询“电子产品”的所有子分类。
WITH RECURSIVE CategoryTree AS (
-- 锚定成员：选择根节点
SELECT id, name, parent_id, 1 AS level
FROM categories
WHERE name = '电子产品'
UNION ALL
-- 递归成员：选择子节点
SELECT c.id, c.name, c.parent_id, ct.level + 1 AS level
FROM categories c
INNER JOIN CategoryTree ct ON c.parent_id = ct.id
)
SELECT id, name, parent_id, level
FROM CategoryTree;这个查询首先使用锚定成员选择name为“电子产品”的分类，并将其level设置为 1。然后，递归成员连接categories表和CategoryTree CTE，找到CategoryTree中节点的子节点，并将它们的level增加 1。递归会一直进行，直到没有更多的子节点可以找到为止。
查询结果如下：
| id | name | parent_id | level |
|---|---|---|---|
| 1 | 电子产品 | NULL | 1 |
| 2 | 电脑 | 1 | 2 |
| 3 | 手机 | 1 | 2 |
| 8 | 配件 | 1 | 2 |
| 4 | 笔记本电脑 | 2 | 3 |
| 5 | 台式机 | 2 | 3 |
| 6 | 智能手机 | 3 | 3 |
| 7 | 功能手机 | 3 | 3 |
| 9 | 键盘 | 8 | 3 |
| 10 | 鼠标 | 8 | 3 |
我们可以看到，查询返回了“电子产品”及其所有子分类的 ID、名称、父 ID 和层级。level列表示节点在树中的深度。
2.2 查询所有父节点
与查询子节点类似，我们也可以使用递归 CTE 查询某个分类的所有父节点。例如，我们要查询“笔记本电脑”的所有父分类。
WITH RECURSIVE CategoryTree AS (
-- 锚定成员：选择目标节点
SELECT id, name, parent_id, 1 AS level
FROM categories
WHERE name = '笔记本电脑'
UNION ALL
-- 递归成员：选择父节点
SELECT c.id, c.name, c.parent_id, ct.level + 1 AS level
FROM categories c
INNER JOIN CategoryTree ct ON c.id = ct.parent_id
)
SELECT id, name, parent_id, level
FROM CategoryTree;这个查询首先选择name为“笔记本电脑”的分类作为锚定成员。然后，递归成员连接categories表和CategoryTree CTE，找到CategoryTree中节点的父节点，并将它们的level增加 1。
查询结果如下：
| id | name | parent_id | level |
|---|---|---|---|
| 4 | 笔记本电脑 | 2 | 1 |
| 2 | 电脑 | 1 | 2 |
| 1 | 电子产品 | NULL | 3 |
2.3 指定起始节点 ID
在实际应用中，我们通常需要根据 ID 而不是名称来查询节点。我们可以修改 CTE 的锚定成员来使用 ID：
WITH RECURSIVE CategoryTree AS (
-- 锚定成员：选择指定 ID 的节点
SELECT id, name, parent_id, 1 AS level
FROM categories
WHERE id = 2 -- 例如，查询 ID 为 2 的节点的所有子节点
UNION ALL
-- 递归成员：选择子节点
SELECT c.id, c.name, c.parent_id, ct.level + 1 AS level
FROM categories c
INNER JOIN CategoryTree ct ON c.parent_id = ct.id
)
SELECT id, name, parent_id, level
FROM CategoryTree;2.4 限制递归深度
有时候，我们可能需要限制递归的深度，以避免无限循环或性能问题。我们可以添加一个WHERE子句到递归成员来限制递归的深度。
WITH RECURSIVE CategoryTree AS (
-- 锚定成员
SELECT id, name, parent_id, 1 AS level
FROM categories
WHERE name = '电子产品'
UNION ALL
-- 递归成员：限制递归深度为 3
SELECT c.id, c.name, c.parent_id, ct.level + 1 AS level
FROM categories c
INNER JOIN CategoryTree ct ON c.parent_id = ct.id
WHERE ct.level < 3 -- 限制 level 小于 3
)
SELECT id, name, parent_id, level
FROM CategoryTree;在这个例子中，我们将递归的深度限制为 3。这意味着查询只会返回根节点及其最多两层子节点。
三、更复杂的分层数据查询
除了基本的父子关系查询外，我们还可以使用 CTE 实现更复杂的分层数据查询。
3.1 计算每个节点的路径
我们可以使用 CTE 计算每个节点从根节点到自身的路径。这可以通过在 CTE 中维护一个路径字符串来实现。
WITH RECURSIVE CategoryTree 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(ct.path, ' > ', c.name) AS path
FROM categories c
INNER JOIN CategoryTree ct ON c.parent_id = ct.id
)
SELECT id, name, parent_id, path
FROM CategoryTree;这个查询首先选择所有根节点，并将它们的路径设置为节点名称。然后，递归成员连接categories表和CategoryTree CTE，将父节点的路径添加到子节点的路径中。CONCAT函数用于连接路径字符串。
查询结果如下：
| id | name | parent_id | path |
|---|---|---|---|
| 1 | 电子产品 | NULL | 电子产品 |
| 2 | 电脑 | 1 | 电子产品 > 电脑 |
| 3 | 手机 | 1 | 电子产品 > 手机 |
| 8 | 配件 | 1 | 电子产品 > 配件 |
| 4 | 笔记本电脑 | 2 | 电子产品 > 电脑 > 笔记本电脑 |
| 5 | 台式机 | 2 | 电子产品 > 电脑 > 台式机 |
| 6 | 智能手机 | 3 | 电子产品 > 手机 > 智能手机 |
| 7 | 功能手机 | 3 | 电子产品 > 手机 > 功能手机 |
| 9 | 键盘 | 8 | 电子产品 > 配件 > 键盘 |
| 10 | 鼠标 | 8 | 电子产品 > 配件 > 鼠标 |
3.2 统计每个节点的子节点数量
我们可以使用 CTE 统计每个节点的子节点数量。这可以通过在 CTE 中维护一个计数器来实现。
WITH RECURSIVE CategoryTree AS (
-- 锚定成员：选择所有节点，初始子节点数量为 0
SELECT id, name, parent_id, 0 AS child_count
FROM categories
UNION ALL
-- 递归成员：更新父节点的子节点数量
SELECT ct.id, ct.name, ct.parent_id, (SELECT COUNT(*) FROM categories WHERE parent_id = ct.id) AS child_count
FROM categories ct
WHERE EXISTS (SELECT 1 FROM categories WHERE parent_id = ct.id)
)
SELECT id, name, parent_id, child_count
FROM CategoryTree
ORDER BY id;这个查询首先选择所有节点，并将它们的子节点数量初始化为 0。然后，递归成员连接categories表和CategoryTree CTE，使用子查询计算每个节点的子节点数量，并更新child_count列。
查询结果如下：
| id | name | parent_id | child_count |
|---|---|---|---|
| 1 | 电子产品 | NULL | 3 |
| 2 | 电脑 | 1 | 2 |
| 3 | 手机 | 1 | 2 |
| 4 | 笔记本电脑 | 2 | 0 |
| 5 | 台式机 | 2 | 0 |
| 6 | 智能手机 | 3 | 0 |
| 7 | 功能手机 | 3 | 0 |
| 8 | 配件 | 1 | 2 |
| 9 | 键盘 | 8 | 0 |
| 10 | 鼠标 | 8 | 0 |
四、性能考虑
虽然 CTE 简化了分层数据查询，但递归 CTE 的性能可能会成为问题，尤其是在处理大型数据集时。以下是一些性能优化的建议：
- 索引: 确保在
parent_id列上创建索引，以加速连接操作。 - 限制递归深度: 使用
WHERE子句限制递归深度，以避免无限循环或性能问题。 - 数据模型优化: 考虑使用其他数据模型，如嵌套集模型或路径枚举模型，这些模型可能更适合某些类型的分层数据查询。
- 物化 CTE: 在某些情况下，可以将 CTE 的结果物化到临时表中，以避免重复计算。但是，这可能会增加查询的复杂性。
- MySQL 版本: 确保使用较新的 MySQL 版本，因为较新的版本通常具有更好的 CTE 性能。MySQL 8.0 及更高版本对 CTE 进行了优化。
五、总结
总而言之，MySQL 的 CTE 提供了一种强大而灵活的方式来处理分层数据。通过递归 CTE，我们可以轻松地查询节点的子节点、父节点、路径和子节点数量。然而，需要注意的是，递归 CTE 的性能可能会成为问题，尤其是在处理大型数据集时。因此，在实际应用中，我们需要根据具体情况选择合适的数据模型和查询策略，并采取相应的性能优化措施。利用好索引和限制递归深度是两个关键的优化手段。通过理解 CTE 的工作原理和性能特点，我们可以更好地利用它来解决复杂的分层数据查询问题。