MySQL的`CTE`（通用表表达式）：如何利用它在递归查询中处理复杂的层次结构数据？

MySQL递归查询：利用CTE处理层次结构数据

大家好，今天我们来深入探讨MySQL中CTE（通用表表达式）的一个重要应用：递归查询。递归查询在处理具有层次结构的数据时非常有用，例如组织架构、评论回复、产品分类等等。我们将通过具体的例子，一步步讲解如何使用CTE实现递归查询，并分析其背后的原理和注意事项。

1. 什么是CTE？

CTE，全称Common Table Expression，即通用表表达式。它可以理解为一个临时的命名结果集，只在当前查询语句中有效。我们可以像使用普通表一样使用CTE，进行SELECT、INSERT、UPDATE、DELETE等操作。 CTE可以是非递归的，也可以是递归的。

2. CTE的基本语法

CTE的基本语法如下：

WITH CTE_name AS (
    SELECT statement
)
SELECT ... FROM CTE_name ... ;

• WITH 关键字用于声明CTE。
• CTE_name 是CTE的名称，用于在后续查询中引用。
• AS 关键字后面跟着一个用括号括起来的SELECT语句，这个SELECT语句的结果集就是CTE的内容。
• 最后，我们可以像使用普通表一样使用CTE，在SELECT语句的FROM子句中引用它。

3. 递归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。
• 递归CTE必须包含两个部分：锚成员和递归成员。
• 锚成员 是一个SELECT语句，用于定义递归的起始点。它通常选择层级结构的根节点或者满足特定条件的初始数据。
• 递归成员 也是一个SELECT语句，它通过引用CTE自身来定义递归的规则。递归成员必须包含一个WHERE子句，用于控制递归的终止条件，避免无限循环。
• UNION ALL 用于将锚成员和递归成员的结果集合并。 使用UNION ALL效率比UNION高，因为UNION会去除重复行。

4. 示例：组织架构查询

假设我们有一个employees表，用于存储员工的信息以及上下级关系，表结构如下：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(255) NOT NULL,
    manager_id INT,  -- 指向manager的employee_id
    title VARCHAR(255),
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);

INSERT INTO employees (employee_id, employee_name, manager_id, title) VALUES
(1, 'John Smith', NULL, 'CEO'),
(2, 'Alice Johnson', 1, 'CTO'),
(3, 'Bob Williams', 1, 'CFO'),
(4, 'Charlie Brown', 2, 'Senior Developer'),
(5, 'David Lee', 2, 'Developer'),
(6, 'Eve Davis', 3, 'Accountant'),
(7, 'Frank Wilson', 3, 'Accountant'),
(8, 'Grace Taylor', 4, 'Junior Developer'),
(9, 'Henry Moore', 5, 'Intern');

这个表的manager_id字段指向employee_id，表示员工的直接上级。如果manager_id为NULL，表示该员工是最高领导者。

4.1 查询所有下属（递归向下）

现在，我们想要查询某个员工的所有下属，包括直接下属和间接下属。例如，查询John Smith（employee_id = 1）的所有下属。我们可以使用递归CTE来实现：

WITH RECURSIVE subordinate_hierarchy AS (
    -- 锚成员：找到John Smith
    SELECT employee_id, employee_name, manager_id, title, 0 AS level
    FROM employees
    WHERE employee_id = 1

    UNION ALL

    -- 递归成员：找到John Smith的下属的下属...
    SELECT e.employee_id, e.employee_name, e.manager_id, e.title, sh.level + 1 AS level
    FROM employees e
    INNER JOIN subordinate_hierarchy sh ON e.manager_id = sh.employee_id
)
SELECT employee_id, employee_name, title, level
FROM subordinate_hierarchy
WHERE employee_id <> 1; -- 排除John Smith本身

这个查询的逻辑如下：

1. 锚成员：选择employees表中employee_id为1的记录（John Smith），作为递归的起始点。level字段初始化为0，表示John Smith的层级。
2. 递归成员：选择employees表中manager_id等于subordinate_hierarchy表中employee_id的记录。也就是说，找到所有直接向subordinate_hierarchy表中的员工汇报的员工。level字段加1，表示下属的层级。
3. UNION ALL 将锚成员和递归成员的结果集合并。
4. 最后，从subordinate_hierarchy CTE中选择需要的字段，并排除John Smith本身。

查询结果如下：

employee_id	employee_name	title	level
2	Alice Johnson	CTO	1
3	Bob Williams	CFO	1
4	Charlie Brown	Senior Developer	2
5	David Lee	Developer	2
6	Eve Davis	Accountant	2
7	Frank Wilson	Accountant	2
8	Grace Taylor	Junior Developer	3
9	Henry Moore	Intern	3

4.2 查询所有上级（递归向上）

反过来，如果我们想要查询某个员工的所有上级，包括直接上级和间接上级。例如，查询Henry Moore（employee_id = 9）的所有上级。我们可以使用类似的递归CTE：

WITH RECURSIVE manager_hierarchy AS (
    -- 锚成员：找到Henry Moore
    SELECT employee_id, employee_name, manager_id, title, 0 AS level
    FROM employees
    WHERE employee_id = 9

    UNION ALL

    -- 递归成员：找到Henry Moore的上级的上级...
    SELECT e.employee_id, e.employee_name, e.manager_id, e.title, mh.level + 1 AS level
    FROM employees e
    INNER JOIN manager_hierarchy mh ON e.employee_id = mh.manager_id
)
SELECT employee_id, employee_name, title, level
FROM manager_hierarchy
WHERE employee_id <> 9; -- 排除Henry Moore本身

这个查询的逻辑与查询下属类似，只是递归成员的连接条件变成了e.employee_id = mh.manager_id，表示找到所有管理manager_hierarchy表中的员工的员工。

查询结果如下：

employee_id	employee_name	title	level
5	David Lee	Developer	1
2	Alice Johnson	CTO	2
1	John Smith	CEO	3

5. 示例：产品分类查询

假设我们有一个categories表，用于存储产品分类的信息，表结构如下：

CREATE TABLE categories (
    category_id INT PRIMARY KEY,
    category_name VARCHAR(255) NOT NULL,
    parent_category_id INT, -- 指向父分类的category_id
    FOREIGN KEY (parent_category_id) REFERENCES categories(category_id)
);

INSERT INTO categories (category_id, category_name, parent_category_id) VALUES
(1, 'Electronics', NULL),
(2, 'Computers', 1),
(3, 'Laptops', 2),
(4, 'Desktops', 2),
(5, 'Accessories', 1),
(6, 'Monitors', 5),
(7, 'Keyboards', 5);

这个表的parent_category_id字段指向category_id，表示父分类。如果parent_category_id为NULL，表示该分类是顶级分类。

5.1 查询某个分类的所有子分类（递归向下）

现在，我们想要查询某个分类的所有子分类，包括直接子分类和间接子分类。例如，查询Electronics（category_id = 1）的所有子分类。

WITH RECURSIVE subcategory_hierarchy AS (
    -- 锚成员：找到Electronics
    SELECT category_id, category_name, parent_category_id, 0 AS level
    FROM categories
    WHERE category_id = 1

    UNION ALL

    -- 递归成员：找到Electronics的子分类的子分类...
    SELECT c.category_id, c.category_name, c.parent_category_id, sh.level + 1 AS level
    FROM categories c
    INNER JOIN subcategory_hierarchy sh ON c.parent_category_id = sh.category_id
)
SELECT category_id, category_name, level
FROM subcategory_hierarchy
WHERE category_id <> 1; -- 排除Electronics本身

查询结果如下：

category_id	category_name	level
2	Computers	1
5	Accessories	1
3	Laptops	2
4	Desktops	2
6	Monitors	2
7	Keyboards	2

5.2 查询某个分类的所有父分类（递归向上）

类似地，我们可以查询某个分类的所有父分类。例如，查询Laptops（category_id = 3）的所有父分类。

WITH RECURSIVE parent_category_hierarchy AS (
    -- 锚成员：找到Laptops
    SELECT category_id, category_name, parent_category_id, 0 AS level
    FROM categories
    WHERE category_id = 3

    UNION ALL

    -- 递归成员：找到Laptops的父分类的父分类...
    SELECT c.category_id, c.category_name, c.parent_category_id, ph.level + 1 AS level
    FROM categories c
    INNER JOIN parent_category_hierarchy ph ON c.category_id = ph.parent_category_id
)
SELECT category_id, category_name, level
FROM parent_category_hierarchy
WHERE category_id <> 3; -- 排除Laptops本身

查询结果如下：

category_id	category_name	level
2	Computers	1
1	Electronics	2

6. 注意事项和最佳实践

• 终止条件： 递归CTE必须包含一个终止条件，以避免无限循环。通常，终止条件是在递归成员的WHERE子句中设置，例如WHERE e.manager_id = sh.employee_id。如果没有满足条件的记录，递归就会停止。

• 性能： 递归CTE的性能可能不如迭代查询，尤其是在处理大型层次结构数据时。因此，应该尽量优化查询语句，例如使用索引、限制递归的深度等。

• 递归深度限制： MySQL默认限制递归的深度为1000。可以使用SET max_execution_time = value来设置最大执行时间，或者使用SET @@cte_max_recursion_depth = value;设置递归深度。

• 避免循环引用： 在设计层次结构数据时，应该避免循环引用，例如A的父节点是B，B的父节点又是A。循环引用会导致递归查询无限循环。

• 使用UNION ALL： 尽量使用UNION ALL而不是UNION，因为UNION ALL不会去除重复行，效率更高。

• 可读性： 为了提高代码的可读性，应该为CTE选择有意义的名称，并添加注释，说明查询的逻辑。

7. 总结

通过以上例子，我们了解了如何使用MySQL的递归CTE处理层次结构数据。递归CTE提供了一种简洁而强大的方式来查询树状结构的数据，例如组织架构、评论回复、产品分类等等。在实际应用中，我们需要根据具体的需求，合理设计表结构和查询语句，并注意性能优化和避免循环引用，以充分发挥递归CTE的优势。
希望通过今天的讲解，大家能够掌握递归CTE的基本原理和用法，并在实际工作中灵活运用。

8. 延伸思考

递归CTE的核心在于定义好锚成员和递归成员，并确保递归能够正确终止。

9. 技巧总结

• 锚成员决定了递归的起点。
• 递归成员决定了递归的规则和方向。
• 务必设置终止条件，防止无限循环。