MySQL 8.0 CTE (Common Table Expressions) 在复杂查询中的应用
大家好，今天我们来深入探讨 MySQL 8.0 引入的一个非常强大的特性：CTE，也就是 Common Table Expressions，中文可以翻译为通用表表达式。CTE 极大地增强了 MySQL 在处理复杂查询方面的能力，提高了代码的可读性和可维护性。
1. 什么是 CTE？
CTE 可以看作是一个临时命名的结果集，它存在于单个查询的执行范围内。 简单来说，它就像一个临时的视图，但与视图不同，CTE 只在定义它的查询中有效。 CTE 不存储任何数据，它只是一个逻辑结构，允许我们将查询分解为更小的、更易于理解的部分。
2. CTE 的语法
CTE 的基本语法如下：
WITH
cte_name1 AS (
SELECT column1, column2
FROM table1
WHERE condition
),
cte_name2 AS (
SELECT column3, column4
FROM table2
WHERE condition
)
SELECT column1, column3
FROM cte_name1
JOIN cte_name2 ON cte_name1.column1 = cte_name2.column3
WHERE condition;- WITH 关键字: CTE 定义的开始标志。
- cte_name1, cte_name2: CTE 的名称，在查询中用于引用这个临时结果集。
- AS (SELECT …): 定义 CTE 的查询语句，这个查询的结果集就是 CTE 的内容。
- SELECT … FROM cte_name1 …: 使用 CTE 的主查询。
一个 WITH 子句可以定义多个 CTE，它们之间用逗号分隔。 多个 CTE 可以互相引用，但需要注意依赖关系，避免循环依赖。
3. CTE 的优势
- 提高可读性: 将复杂的查询分解为更小的、逻辑上独立的 CTE，使查询更易于理解和维护。
- 代码复用: CTE 可以在单个查询中被多次引用，避免重复编写相同的子查询。
- 简化递归查询: CTE 支持递归定义，可以方便地处理树状结构或层级关系的数据。
- 逻辑清晰: CTE 允许我们为中间结果赋予有意义的名称，从而更清晰地表达查询的意图。
- 避免子查询嵌套过深: 通过将子查询转换为 CTE，可以避免子查询嵌套过深，提高查询性能。
4. CTE 的类型
MySQL 8.0 支持两种类型的 CTE：
- Non-recursive CTE (非递归 CTE): 最常见的 CTE 类型，用于定义不涉及自身引用的临时结果集。
- Recursive CTE (递归 CTE): 用于定义可以引用自身的临时结果集，常用于处理层级结构的数据。
5. Non-recursive CTE 的应用
我们通过一些例子来说明 Non-recursive CTE 的应用。
例 1: 计算每个部门的平均工资，并找出工资高于部门平均工资的员工。
假设我们有以下两个表：
employees(员工表):employee_id,employee_name,department_id,salarydepartments(部门表):department_id,department_name
如果没有 CTE，我们需要使用嵌套子查询来实现：
SELECT
e.employee_name,
e.salary,
d.department_name
FROM
employees e
JOIN
departments d ON e.department_id = d.department_id
WHERE
e.salary > (
SELECT AVG(salary)
FROM employees
WHERE department_id = e.department_id
);使用 CTE，代码更清晰：
WITH
DepartmentAvgSalary AS (
SELECT
department_id,
AVG(salary) AS avg_salary
FROM
employees
GROUP BY
department_id
)
SELECT
e.employee_name,
e.salary,
d.department_name
FROM
employees e
JOIN
departments d ON e.department_id = d.department_id
JOIN
DepartmentAvgSalary das ON e.department_id = das.department_id
WHERE
e.salary > das.avg_salary;在这个例子中，DepartmentAvgSalary CTE 计算了每个部门的平均工资，然后在主查询中，我们将 employees 表和 departments 表与 DepartmentAvgSalary CTE 连接，找到工资高于部门平均工资的员工。 代码可读性明显提升。
例 2: 找出销售额排名前 5 的客户。
假设我们有以下两个表：
customers(客户表):customer_id,customer_nameorders(订单表):order_id,customer_id,order_amount
WITH
CustomerTotalSales AS (
SELECT
customer_id,
SUM(order_amount) AS total_sales
FROM
orders
GROUP BY
customer_id
),
RankedCustomers AS (
SELECT
customer_id,
total_sales,
RANK() OVER (ORDER BY total_sales DESC) AS sales_rank
FROM
CustomerTotalSales
)
SELECT
c.customer_name,
rc.total_sales,
rc.sales_rank
FROM
customers c
JOIN
RankedCustomers rc ON c.customer_id = rc.customer_id
WHERE
rc.sales_rank <= 5;在这个例子中，我们使用了两个 CTE。 CustomerTotalSales CTE 计算了每个客户的总销售额。 RankedCustomers CTE 使用 RANK() 窗口函数对客户按照总销售额进行排名。 最后，我们在主查询中，将 customers 表和 RankedCustomers CTE 连接，找到销售额排名前 5 的客户。
6. Recursive CTE 的应用
Recursive CTE 用于处理层级结构的数据，例如组织结构、目录结构、族谱等。 递归 CTE 包含两个部分：
- Anchor Member (锚点成员): 定义递归的起始点，通常是一个 SELECT 语句，返回递归的初始结果集。
- Recursive Member (递归成员): 定义递归的规则，也是一个 SELECT 语句，它引用 CTE 自身，并将结果与锚点成员的结果集合并。
递归 CTE 使用 UNION ALL 将锚点成员和递归成员的结果集合并。 递归会一直进行，直到递归成员返回空结果集。
例 1: 查找某个员工的所有下属。
假设我们有一个 employees 表，包含员工的 ID、姓名和上级 ID：
employees(员工表):employee_id,employee_name,manager_id
WITH RECURSIVE
EmployeeHierarchy AS (
-- Anchor member: 找到顶级员工 (没有 manager)
SELECT
employee_id,
employee_name,
manager_id,
1 AS level
FROM
employees
WHERE
manager_id IS NULL
UNION ALL
-- Recursive member: 找到所有下属
SELECT
e.employee_id,
e.employee_name,
e.manager_id,
eh.level + 1 AS level
FROM
employees e
JOIN
EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT
employee_id,
employee_name,
manager_id,
level
FROM
EmployeeHierarchy
WHERE
employee_id = 10; -- 例如，查找 employee_id 为 10 的员工的所有下属在这个例子中，锚点成员选择了所有 manager_id 为 NULL 的员工，也就是顶级员工。 递归成员选择了所有 manager_id 等于 EmployeeHierarchy 中 employee_id 的员工，也就是下属员工。 递归成员将 level 字段加 1，表示下属的层级。 UNION ALL 将锚点成员和递归成员的结果集合并，直到没有更多的下属被找到。最后查询中增加WHERE条件，可以查出employee_id为10的所有下属。
例 2: 生成一个数字序列。
WITH RECURSIVE
NumberSequence AS (
-- Anchor member: 起始数字
SELECT 1 AS n
UNION ALL
-- Recursive member: 递增数字
SELECT n + 1
FROM NumberSequence
WHERE n < 10
)
SELECT n FROM NumberSequence;这个例子生成了一个从 1 到 10 的数字序列。 锚点成员选择了数字 1。 递归成员选择了 NumberSequence 中 n 加 1 的数字，直到 n 大于等于 10。
7. CTE 的注意事项
- 性能: 虽然 CTE 可以提高代码的可读性，但并不一定能提高查询性能。 MySQL 优化器会对 CTE 进行优化，但有时 CTE 可能会导致性能下降。 在性能敏感的场景下，需要仔细评估 CTE 的性能影响。
- 循环引用: 多个 CTE 之间不能存在循环引用，否则会导致错误。
- 命名冲突: CTE 的名称不能与表名或视图名冲突。
- 作用域: CTE 的作用域仅限于定义它的查询。 在同一个
WITH子句中定义的 CTE 可以互相引用，但不能在不同的WITH子句中引用。 - 数据修改: 从 MySQL 8.0 开始， CTE 可以用于
INSERT,UPDATE,DELETE语句中，可以简化复杂的数据修改操作。
8. CTE 在数据修改中的应用
例 1: 删除所有重复的记录。
假设我们有一个 duplicates 表，包含重复的记录：
duplicates(重复记录表):id,value
WITH
DuplicateRows AS (
SELECT
id,
value,
ROW_NUMBER() OVER (PARTITION BY value ORDER BY id) AS row_num
FROM
duplicates
)
DELETE FROM duplicates
WHERE id IN (SELECT id FROM DuplicateRows WHERE row_num > 1);在这个例子中，DuplicateRows CTE 使用 ROW_NUMBER() 窗口函数为每个 value 分组内的记录分配一个行号。 然后，我们在 DELETE 语句中，删除所有行号大于 1 的记录，也就是重复的记录。
例 2: 更新某个部门所有员工的工资。
WITH
DepartmentEmployees AS (
SELECT employee_id
FROM employees
WHERE department_id = 1
)
UPDATE employees
SET salary = salary * 1.1
WHERE employee_id IN (SELECT employee_id FROM DepartmentEmployees);在这个例子中，DepartmentEmployees CTE 选择了 department_id 为 1 的所有员工的 employee_id。 然后，我们在 UPDATE 语句中，将这些员工的工资提高 10%。
9. CTE 与视图的比较
| 特性 | CTE | 视图 |
|---|---|---|
| 持久性 | 临时性的，只在查询中有效 | 持久性的，存储在数据库中 |
| 可重用性 | 只能在定义它的查询中使用 | 可以在多个查询中使用 |
| 存储数据 | 不存储数据，只是逻辑结构 | 不存储数据，只是逻辑结构 |
| 修改数据 | 可以用于修改数据 | 通常用于只读查询 |
| 适用场景 | 复杂的、一次性的查询 | 经常使用的、通用的查询 |
10. CTE 优化建议
- 尽量避免在 CTE 中进行复杂计算: 将复杂计算放在主查询中，可以提高 CTE 的执行效率。
- 合理使用索引: 确保 CTE 中使用的表有适当的索引，以提高查询性能。
- 避免在递归 CTE 中进行全表扫描: 在递归 CTE 中，尽量使用索引或 WHERE 子句来限制递归的范围，避免全表扫描。
- 测试 CTE 的性能: 在性能敏感的场景下，需要测试 CTE 的性能，并与不使用 CTE 的查询进行比较，选择最佳的方案。
总结：灵活运用CTE简化复杂查询，提高代码可读性
CTE 是 MySQL 8.0 中一个非常强大的特性，可以极大地简化复杂查询，提高代码的可读性和可维护性。无论是处理层级结构的数据，还是进行复杂的数据转换，CTE 都能提供简洁而优雅的解决方案。 掌握 CTE 的使用方法，可以使我们编写出更高效、更易于理解的 SQL 代码。在实际开发中，我们需要根据具体的场景选择合适的 CTE 类型，并注意 CTE 的性能影响，以充分发挥 CTE 的优势。