MySQL 8.0 CTE：SQL查询的递归与可读性
大家好，今天我们来深入探讨MySQL 8.0中一个非常重要的特性：公共表表达式（Common Table Expressions，简称CTE）。CTE不仅能显著提升SQL查询的可读性，更重要的是，它引入了递归查询的能力，使得我们能够处理诸如树形结构、层级关系等复杂的数据结构。
CTE 的基本概念
CTE本质上是一个命名的临时结果集，它只在单个查询语句的执行范围内有效。你可以把它想象成一个临时视图，但它比视图更灵活，因为它不需要持久化存储，并且可以递归定义。
CTE 的语法
CTE的语法结构如下：
WITH
cte_name AS (
SELECT column1, column2, ...
FROM table_name
WHERE condition
)
SELECT column1, column2, ...
FROM cte_name
WHERE condition;WITH关键字标志着CTE的开始。cte_name是你为CTE定义的名称，后续的查询语句可以通过这个名称引用CTE的结果集。AS关键字后面跟着用括号括起来的SELECT语句，这个SELECT语句定义了CTE的查询逻辑。- 最后，你可以使用
SELECT语句来查询CTE的结果集，就像查询普通表一样。
CTE 的优势
使用CTE的主要优势包括：
- 提高可读性: 将复杂的查询逻辑分解成多个小的、逻辑上独立的CTE，可以使SQL查询更容易理解和维护。
- 代码重用: 可以在同一个查询语句中多次引用同一个CTE，避免重复编写相同的查询逻辑。
- 简化复杂查询: 对于包含多个子查询或连接的复杂查询，使用CTE可以使查询结构更清晰，更容易调试。
- 支持递归查询: 这是CTE最强大的功能之一，允许我们处理层级结构的数据。
CTE 的实际应用
我们通过几个例子来演示CTE的实际应用。
1. 简化复杂查询
假设我们有一个 employees 表，包含员工的ID、姓名、部门和薪水。我们需要找到薪水高于部门平均薪水的员工。
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(255),
department VARCHAR(255),
salary DECIMAL(10, 2)
);
INSERT INTO employees (employee_id, employee_name, department, salary) VALUES
(1, 'Alice', 'Sales', 60000.00),
(2, 'Bob', 'Sales', 50000.00),
(3, 'Charlie', 'Marketing', 70000.00),
(4, 'David', 'Marketing', 65000.00),
(5, 'Eve', 'Engineering', 80000.00),
(6, 'Frank', 'Engineering', 75000.00);不使用CTE的SQL查询可能如下所示：
SELECT e.employee_name, e.salary, e.department
FROM employees e
JOIN (
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
) AS dept_avg ON e.department = dept_avg.department
WHERE e.salary > dept_avg.avg_salary;使用CTE，我们可以将计算部门平均薪水的逻辑提取出来：
WITH dept_avg AS (
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
)
SELECT e.employee_name, e.salary, e.department
FROM employees e
JOIN dept_avg ON e.department = dept_avg.department
WHERE e.salary > dept_avg.avg_salary;可以看到，使用CTE后的查询语句更易读，结构更清晰。
2. 代码重用
假设我们需要统计每个部门薪水高于部门平均薪水的员工人数，以及薪水低于部门平均薪水的员工人数。
不使用CTE，我们需要重复计算部门平均薪水：
SELECT
department,
SUM(CASE WHEN salary > dept_avg.avg_salary THEN 1 ELSE 0 END) AS above_avg_count,
SUM(CASE WHEN salary < dept_avg.avg_salary THEN 1 ELSE 0 END) AS below_avg_count
FROM employees e
JOIN (
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
) AS dept_avg ON e.department = dept_avg.department
GROUP BY department;使用CTE，我们可以只计算一次部门平均薪水，然后在多个地方引用它：
WITH dept_avg AS (
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
)
SELECT
e.department,
SUM(CASE WHEN e.salary > da.avg_salary THEN 1 ELSE 0 END) AS above_avg_count,
SUM(CASE WHEN e.salary < da.avg_salary THEN 1 ELSE 0 END) AS below_avg_count
FROM employees e
JOIN dept_avg da ON e.department = da.department
GROUP BY e.department;3. 递归 CTE
这是CTE最强大的特性。递归CTE允许我们处理层级结构的数据，例如组织架构、树形结构等。
3.1 递归 CTE 的语法
递归CTE的语法与普通CTE略有不同：
WITH RECURSIVE cte_name AS (
-- 锚定成员 (Anchor Member)
SELECT column1, column2, ...
FROM table_name
WHERE condition
UNION ALL
-- 递归成员 (Recursive Member)
SELECT column1, column2, ...
FROM table_name, cte_name
WHERE condition
)
SELECT column1, column2, ...
FROM cte_name
WHERE condition;WITH RECURSIVE关键字标志着递归CTE的开始。- 递归CTE必须包含两个部分：锚定成员 (Anchor Member) 和 递归成员 (Recursive Member)。
- 锚定成员 是递归的起始点，它是一个
SELECT语句，用于初始化递归结果集。 - 递归成员 是递归的迭代步骤，它也是一个
SELECT语句，用于从前一次迭代的结果集中获取数据，并生成新的结果集。 UNION ALL关键字用于将锚定成员和递归成员的结果集合并起来。必须使用UNION ALL， 而不是UNION，否则可能会导致递归无法终止。- 递归成员必须引用CTE自身，这是递归的关键。
- 递归过程会一直进行，直到递归成员返回空结果集为止。
3.2 递归 CTE 的应用
我们通过一个例子来演示递归CTE的应用。假设我们有一个 employee_hierarchy 表，包含员工的ID、姓名和经理的ID。
CREATE TABLE employee_hierarchy (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(255),
manager_id INT,
FOREIGN KEY (manager_id) REFERENCES employee_hierarchy(employee_id)
);
INSERT INTO employee_hierarchy (employee_id, employee_name, manager_id) VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 1),
(4, 'David', 2),
(5, 'Eve', 2),
(6, 'Frank', 3);现在，我们需要查询每个员工的所有下属，包括直接下属和间接下属。
WITH RECURSIVE subordinate_hierarchy AS (
-- 锚定成员: 找到最高级别的经理 (没有经理)
SELECT employee_id, employee_name, manager_id, 0 AS level
FROM employee_hierarchy
WHERE manager_id IS NULL
UNION ALL
-- 递归成员: 找到每个经理的下属
SELECT e.employee_id, e.employee_name, e.manager_id, sh.level + 1 AS level
FROM employee_hierarchy e
JOIN subordinate_hierarchy sh ON e.manager_id = sh.employee_id
)
SELECT employee_id, employee_name, manager_id, level
FROM subordinate_hierarchy
ORDER BY level, employee_name;这个查询的执行过程如下：
- 锚定成员 首先找到最高级别的经理（
Alice），并将其添加到结果集中。level设置为 0。 - 递归成员 然后找到
Alice的所有下属（Bob和Charlie），并将它们添加到结果集中。level设置为 1。 - 递归成员 再次找到
Bob和Charlie的所有下属（David、Eve和Frank），并将它们添加到结果集中。level设置为 2。 - 递归过程一直进行，直到没有更多的下属可以找到为止。
查询结果如下：
| employee_id | employee_name | manager_id | level |
|---|---|---|---|
| 1 | Alice | NULL | 0 |
| 2 | Bob | 1 | 1 |
| 3 | Charlie | 1 | 1 |
| 4 | David | 2 | 2 |
| 5 | Eve | 2 | 2 |
| 6 | Frank | 3 | 2 |
通过这个例子，我们可以看到递归CTE在处理层级结构数据方面的强大能力。
4. 查找路径
我们还可以使用递归 CTE 来查找树形结构中两个节点之间的路径。例如，假设我们有一个 category 表，表示产品的分类结构。
CREATE TABLE category (
category_id INT PRIMARY KEY,
category_name VARCHAR(255),
parent_category_id INT,
FOREIGN KEY (parent_category_id) REFERENCES category(category_id)
);
INSERT INTO category (category_id, category_name, parent_category_id) VALUES
(1, 'Electronics', NULL),
(2, 'Computers', 1),
(3, 'Laptops', 2),
(4, 'Desktops', 2),
(5, 'Accessories', 1),
(6, 'Mice', 5);我们需要找到从 Mice (category_id = 6) 到 Electronics (category_id = 1) 的路径。
WITH RECURSIVE category_path AS (
-- 锚定成员: 找到目标节点
SELECT category_id, category_name, parent_category_id, CAST(category_name AS CHAR(255)) AS path
FROM category
WHERE category_id = 6
UNION ALL
-- 递归成员: 向上追溯父节点
SELECT c.category_id, c.category_name, c.parent_category_id, CONCAT(c.category_name, ' -> ', cp.path) AS path
FROM category c
JOIN category_path cp ON c.category_id = cp.parent_category_id
)
SELECT path
FROM category_path
WHERE category_id = 1;这个查询的执行过程如下：
- 锚定成员 首先找到目标节点
Mice，并将其category_name作为初始路径。 - 递归成员 然后向上追溯
Mice的父节点Accessories，并将Accessories的category_name添加到路径中。 - 递归成员 再次向上追溯
Accessories的父节点Electronics，并将Electronics的category_name添加到路径中。 - 当到达
Electronics节点时，查询结束。
查询结果如下：
| path |
|---|
| Electronics -> Accessories -> Mice |
CTE 的性能考量
虽然CTE可以提高SQL查询的可读性和灵活性，但在性能方面需要注意以下几点：
- 物化 (Materialization): MySQL可能会将CTE的结果集物化（即将其存储到临时表中），这可能会导致性能下降，尤其是在处理大量数据时。 但是，MySQL的优化器会尝试避免不必要的物化，特别是在CTE只被引用一次的情况下。
- 索引: 如果CTE的查询需要访问大量数据，确保相关的表上存在适当的索引，以提高查询效率。
- 递归深度: 对于递归CTE，需要注意递归深度，避免无限循环或超出MySQL的递归深度限制。MySQL默认的递归深度限制是1000，可以通过
max_execution_time系统变量来控制。 - 数据量: 递归CTE的性能会受到数据量的影响。如果数据量很大，可以考虑使用其他方法来处理层级结构的数据，例如预计算或使用专门的图数据库。
使用CTE的注意事项
- 作用域: CTE只在当前查询语句中有效。
- 命名冲突: CTE的名称不能与同一查询语句中的其他表或CTE的名称冲突。
- 不允许
ORDER BY和LIMIT: 在CTE的定义中，通常不允许使用ORDER BY和LIMIT子句，除非在MySQL 8.0.19及更高版本中，可以在CTE中使用ORDER BY和LIMIT，但仅当CTE是SELECT语句的最外层查询时才有效。
总结： CTE带来的查询优化和扩展
CTE是MySQL 8.0中一个非常强大的特性，它不仅可以提高SQL查询的可读性和可维护性，还可以简化复杂查询，支持递归查询。合理使用CTE可以使我们的SQL代码更清晰、更高效，从而更好地处理各种数据分析和处理任务。通过锚定成员和递归成员的结合，递归CTE能够处理层级数据，而普通CTE则通过分解复杂逻辑和代码复用，增强了SQL的可读性和组织性。