利用 MySQL CTE 简化复杂递归查询
大家好，今天我们来聊聊 MySQL 中 CTE（Common Table Expressions），特别是如何利用它来简化复杂的递归查询。递归查询在处理具有层级关系的数据时非常有用，例如组织架构、商品分类、家谱关系等等。但是，传统的递归查询往往比较复杂，难以理解和维护。CTE 的引入，特别是递归 CTE，为我们提供了一种更清晰、更易于管理的方式来处理这类问题。
什么是 CTE？
CTE，即 Common Table Expression，公共表表达式，可以理解为一个临时的、命名的结果集，它只在当前查询语句中有效。可以把它看作一个临时的视图，但它比视图更灵活，因为它可以在查询中多次引用，并且支持递归。
CTE 的基本语法如下：
WITH cte_name AS (
SELECT ... -- CTE 的定义
)
SELECT ... -- 使用 CTE 的查询其中，cte_name 是 CTE 的名称，SELECT ... 是 CTE 的定义，它定义了 CTE 结果集的结构和数据。后面的 SELECT ... 则是使用 CTE 的查询，它可以像一个普通的表一样引用 CTE。
CTE 的优势
- 提高可读性： 将复杂的查询分解为更小的、逻辑独立的 CTE，使代码更易于理解。
- 代码重用： CTE 可以在查询中多次引用，避免重复编写相同的子查询。
- 简化递归查询： 递归 CTE 允许在 CTE 的定义中引用自身，从而实现递归查询，这比传统的递归查询更易于编写和维护。
- 提高性能（某些情况下）： 虽然 CTE 本身不一定会直接提升性能，但通过提高可读性和简化逻辑，我们可以更容易地对查询进行优化。
递归 CTE 的基本结构
递归 CTE 的核心在于它能够引用自身。一个递归 CTE 通常由两部分组成：
- 锚定成员（Anchor Member）： 这是一个非递归的
SELECT语句，它定义了递归的起始点。 - 递归成员（Recursive Member）： 这是一个
SELECT语句，它引用 CTE 自身，并定义了递归的步骤。
这两部分通过 UNION ALL 连接起来。UNION ALL 保证了所有满足条件的结果都会被包含在最终结果集中。
递归 CTE 的基本语法如下：
WITH RECURSIVE cte_name AS (
-- 锚定成员
SELECT ...
UNION ALL
-- 递归成员
SELECT ... FROM cte_name WHERE ...
)
SELECT ... FROM cte_name; -- 使用 CTE 的查询需要注意的是，递归成员必须包含一个 WHERE 子句，用于控制递归的终止条件，避免无限循环。
案例一：查找所有下属员工
假设我们有一个 employees 表，用于存储员工信息，其中包含 employee_id (员工ID), employee_name (员工姓名), 和 manager_id (经理ID) 字段。现在我们需要查找某个员工的所有下属员工。
表结构：
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(255),
manager_id INT
);
INSERT INTO employees (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),
(7, 'Grace', 4);使用递归 CTE 查询 Alice 的所有下属：
WITH RECURSIVE subordinates AS (
-- 锚定成员：找到 Alice 本人
SELECT employee_id, employee_name, manager_id
FROM employees
WHERE employee_name = 'Alice'
UNION ALL
-- 递归成员：找到所有 manager_id 等于上级员工 employee_id 的员工
SELECT e.employee_id, e.employee_name, e.manager_id
FROM employees e
INNER JOIN subordinates s ON e.manager_id = s.employee_id
)
-- 查询结果
SELECT * FROM subordinates WHERE employee_name <> 'Alice';代码解释：
WITH RECURSIVE subordinates AS (...): 定义一个名为subordinates的递归 CTE。- 锚定成员:
SELECT employee_id, employee_name, manager_id FROM employees WHERE employee_name = 'Alice'从employees表中选择employee_name为 ‘Alice’ 的记录，作为递归的起始点。 - 递归成员:
SELECT e.employee_id, e.employee_name, e.manager_id FROM employees e INNER JOIN subordinates s ON e.manager_id = s.employee_id将employees表（别名为e）与subordinatesCTE（别名为s）进行INNER JOIN，连接条件是e.manager_id = s.employee_id。 这意味着它会找到所有manager_id等于subordinatesCTE 中employee_id的员工，并将它们添加到subordinatesCTE 中。 - *`SELECT FROM subordinates WHERE employee_name <> ‘Alice’
**: 从subordinates` CTE 中选择所有记录，并排除 Alice 本人。
查询结果：
| employee_id | employee_name | manager_id |
|---|---|---|
| 2 | Bob | 1 |
| 3 | Charlie | 1 |
| 4 | David | 2 |
| 5 | Eve | 2 |
| 6 | Frank | 3 |
| 7 | Grace | 4 |
案例二：计算商品分类的层级路径
假设我们有一个 categories 表，用于存储商品分类信息，其中包含 category_id (分类ID), category_name (分类名称), 和 parent_id (父分类ID) 字段。现在我们需要计算每个分类的层级路径，例如 "电子产品 > 手机 > 智能手机"。
表结构：
CREATE TABLE categories (
category_id INT PRIMARY KEY,
category_name VARCHAR(255),
parent_id INT
);
INSERT INTO categories (category_id, category_name, parent_id) VALUES
(1, '电子产品', NULL),
(2, '手机', 1),
(3, '智能手机', 2),
(4, '家用电器', NULL),
(5, '电视', 4),
(6, '冰箱', 4);使用递归 CTE 计算分类层级路径：
WITH RECURSIVE category_path AS (
-- 锚定成员：找到所有顶级分类（parent_id 为 NULL）
SELECT category_id, category_name, parent_id, category_name AS path
FROM categories
WHERE parent_id IS NULL
UNION ALL
-- 递归成员：将子分类的名称添加到父分类的路径中
SELECT c.category_id, c.category_name, c.parent_id, CONCAT(cp.path, ' > ', c.category_name) AS path
FROM categories c
INNER JOIN category_path cp ON c.parent_id = cp.category_id
)
-- 查询结果
SELECT category_id, category_name, path FROM category_path;代码解释：
WITH RECURSIVE category_path AS (...): 定义一个名为category_path的递归 CTE。- 锚定成员:
SELECT category_id, category_name, parent_id, category_name AS path FROM categories WHERE parent_id IS NULL从categories表中选择所有parent_id为NULL的记录（即顶级分类），并将分类名称作为初始路径。 - 递归成员:
SELECT c.category_id, c.category_name, c.parent_id, CONCAT(cp.path, ' > ', c.category_name) AS path FROM categories c INNER JOIN category_path cp ON c.parent_id = cp.category_id将categories表（别名为c）与category_pathCTE（别名为cp）进行INNER JOIN，连接条件是c.parent_id = cp.category_id。 这会将子分类的名称添加到父分类的路径中，使用CONCAT函数将父分类的路径和子分类的名称连接起来，并用 " > " 分隔。 SELECT category_id, category_name, path FROM category_path: 从category_pathCTE 中选择所有记录，包括分类 ID、分类名称和层级路径。
查询结果：
| category_id | category_name | path |
|---|---|---|
| 1 | 电子产品 | 电子产品 |
| 4 | 家用电器 | 家用电器 |
| 2 | 手机 | 电子产品 > 手机 |
| 5 | 电视 | 家用电器 > 电视 |
| 6 | 冰箱 | 家用电器 > 冰箱 |
| 3 | 智能手机 | 电子产品 > 手机 > 智能手机 |
案例三：家谱关系查询
假设我们有一个 family 表，用于存储家谱信息，其中包含 person_id (人物ID), person_name (人物姓名), 和 parent_id (父亲ID) 字段。现在我们需要查找某个人的所有祖先。
表结构：
CREATE TABLE family (
person_id INT PRIMARY KEY,
person_name VARCHAR(255),
parent_id INT
);
INSERT INTO family (person_id, person_name, parent_id) VALUES
(1, 'Adam', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 2),
(4, 'David', 3),
(5, 'Eve', 4);使用递归 CTE 查询 David 的所有祖先：
WITH RECURSIVE ancestors AS (
-- 锚定成员：找到 David 本人
SELECT person_id, person_name, parent_id, 0 AS generation
FROM family
WHERE person_name = 'David'
UNION ALL
-- 递归成员：找到所有 parent_id 等于上级人物 person_id 的人物
SELECT f.person_id, f.person_name, f.parent_id, a.generation + 1
FROM family f
INNER JOIN ancestors a ON f.person_id = a.parent_id
)
-- 查询结果
SELECT * FROM ancestors WHERE person_name <> 'David';代码解释：
WITH RECURSIVE ancestors AS (...): 定义一个名为ancestors的递归 CTE。- 锚定成员:
SELECT person_id, person_name, parent_id, 0 AS generation FROM family WHERE person_name = 'David'从family表中选择person_name为 ‘David’ 的记录，并将 generation 设置为 0，作为递归的起始点。 - 递归成员:
SELECT f.person_id, f.person_name, f.parent_id, a.generation + 1 FROM family f INNER JOIN ancestors a ON f.person_id = a.parent_id将family表（别名为f）与ancestorsCTE（别名为a）进行INNER JOIN，连接条件是f.person_id = a.parent_id。 这意味着它会找到所有person_id等于ancestorsCTE 中parent_id的人物，并将它们添加到ancestorsCTE 中。 generation 加 1。 - *`SELECT FROM ancestors WHERE person_name <> ‘David’
**: 从ancestors` CTE 中选择所有记录，并排除 David 本人。
查询结果：
| person_id | person_name | parent_id | generation |
|---|---|---|---|
| 3 | Charlie | 2 | 1 |
| 2 | Bob | 1 | 2 |
| 1 | Adam | NULL | 3 |
注意事项
- 避免无限循环： 递归 CTE 中必须包含一个终止条件，以避免无限循环。通常，这个终止条件体现在递归成员的
WHERE子句中。 - 性能考虑： 递归 CTE 在处理大量数据时可能会影响性能。在实际应用中，需要仔细评估性能，并考虑是否可以使用其他方法来优化查询。
- MySQL 版本限制： 递归 CTE 在 MySQL 8.0 及更高版本中可用。
总结：CTE 简化递归查询的优势
通过以上案例，我们可以看到，递归 CTE 可以有效地简化复杂的递归查询，提高代码的可读性和可维护性。它将复杂的逻辑分解为更小的、逻辑独立的单元，使我们能够更清晰地理解查询的意图。
总结：CTE 提高了代码的可读性和可维护性
总而言之，CTE 是 MySQL 中一个强大的工具，可以用于简化复杂的查询，特别是递归查询。 它可以提高代码的可读性、可维护性，并使我们能够更轻松地处理具有层级关系的数据。掌握 CTE 的使用方法，对于提升 SQL 开发效率和编写高质量的 SQL 代码至关重要。