MySQL 8.0 CTE公用表表达式深度实战：递归查询、层级数据处理与性能优化

什么是CTE（公用表表达式）

公用表表达式（Common Table Expression，简称CTE）是SQL标准中定义的一种临时结果集机制，它在查询执行期间存在，作用范围限于包含它的单条SQL语句。与传统的子查询和临时表相比，CTE提供了更好的可读性、可维护性，并且在递归场景下有着不可替代的优势。

MySQL 8.0 正式引入了CTE支持，包括非递归CTE和递归CTE两种形式。这一特性填补了MySQL在层级数据查询、树形结构遍历等方面的长期短板，使得许多过去需要应用层代码或存储过程才能实现的逻辑，现在可以用纯SQL优雅地完成。

CTE的核心语法使用

关键字定义：

1
2
3
4
WITH cte_name AS (

    SELECT ...

)

SELECT * FROM cte_name;

一个CTE本质上是一个命名的临时结果集。你可以把它想象成在查询内部定义了一个”视图”，但它的生命周期仅限于当前语句。与子查询不同，CTE可以被在同一条语句中多次引用，这避免了子查询的重复编写和重复执行（在非递归场景下）。

非递归CTE：提升SQL可读性与复用性

基本语法与多CTE定义

非递归CTE是最常用的形式。它允许你在主查询之前定义一个或多个临时结果集，使复杂查询的逻辑更加清晰：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- 单个CTE

WITH department_stats AS (

    SELECT 

        dept_id,

        COUNT(*) AS employee_count,

        AVG(salary) AS avg_salary

    FROM employees

    GROUP BY dept_id

)

SELECT d.dept_name, ds.employee_count, ds.avg_salary

FROM departments d

JOIN department_stats ds ON d.id = ds.dept_id

ORDER BY ds.avg_salary DESC;



-- 多个CTE（用逗号分隔）

WITH 

high_salary AS (

    SELECT * FROM employees WHERE salary > 50000

),

dept_avg AS (

    SELECT dept_id, AVG(salary) AS avg_sal

    FROM employees

    GROUP BY dept_id

)

SELECT h.name, h.salary, d.avg_sal,

       h.salary - d.avg_sal AS above_avg

FROM high_salary h

JOIN dept_avg d ON h.dept_id = d.dept_id;

CTE vs 子查询 vs 临时表

三者都能实现类似的功能，但各有优劣：

实际选择建议：对于复杂分析查询优先使用CTE；对于需要跨语句共享或建立索引的大数据集使用临时表；简单过滤条件可直接用子查询。

CTE在数据清洗中的实际应用

在ETL或数据清洗场景中，CTE可以清晰地将多个处理步骤串联起来：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
WITH 

-- 第1步：去除重复记录

deduplicated AS (

    SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY updated_at DESC) AS rn

    FROM raw_users

),

-- 第2步：筛选有效记录

cleaned AS (

    SELECT * FROM deduplicated WHERE rn = 1 AND email IS NOT NULL

),

-- 第3步：标准化字段

normalized AS (

    SELECT 

        id,

        LOWER(TRIM(email)) AS email,

        CONCAT(UPPER(LEFT(name,1)), LOWER(SUBSTRING(name,2))) AS name,

        DATE(created_at) AS register_date

    FROM cleaned

)

-- 第4步：最终输出

SELECT * FROM normalized WHERE email LIKE '%@%.%';

这种分步写法让每个步骤的职责清晰，便于调试和维护。如果某一步的结果不符合预期，只需单独注释掉后续步骤，查看该步CTE的输出即可定位问题。

递归CTE：层级数据与树形结构的终极武器

递归CTE的工作原理

递归CTE是CTE最强大的形式，它由两部分组成：锚定成员（Anchor Member）和递归成员（Recursive Member），通过

连接：

1
2
3
4
5
6
7
8
9
10
WITH RECURSIVE cte_name AS (

    -- 锚定成员：非递归的初始查询（种子行）

    SELECT ... FROM table WHERE condition

    

    UNION ALL

    

    -- 递归成员：引用自身，逐层扩展

    SELECT ... FROM table JOIN cte_name ON ...

)

SELECT * FROM cte_name;

执行过程如下：

• 1. 执行锚定成员，产生初始结果集（第0层）
• 2. 将初始结果集作为输入，执行递归成员，产生第1层结果
• 3. 将第1层结果作为输入，继续执行递归成员，产生第2层结果
• 4. 重复此过程直到递归成员返回空结果集
• 5. 将所有层的结果通过UNION ALL合并为最终结果

实战：组织架构树遍历

这是递归CTE最经典的应用场景——查询某个员工的所有下属（包括间接下属）：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 表结构

-- employees(id, name, manager_id, department)

-- manager_id 指向直属上级的 id



WITH RECURSIVE subordinates AS (

    -- 锚定：从CEO开始（manager_id IS NULL）

    SELECT id, name, manager_id, department, 0 AS level, CAST(id AS CHAR(200)) AS path

    FROM employees

    WHERE id = 1  -- 从指定人员开始

    

    UNION ALL

    

    -- 递归：查找当前层每个员工的直属下级

    SELECT e.id, e.name, e.manager_id, e.department,

           s.level + 1 AS level,

           CONCAT(s.path, '->', e.id) AS path

    FROM employees e

    JOIN subordinates s ON e.manager_id = s.id

)

SELECT * FROM subordinates ORDER BY level, department;

在这个查询中，我们同时维护了

（层级深度）和

（从根到当前节点的路径），这对于理解组织架构关系至关重要。CAST(path AS CHAR(200)) 是必须的，因为递归成员的列类型必须与锚定成员一致。

实战：商品分类无限级联

电商系统的分类通常是无限层级的树形结构，递归CTE可以高效地处理这类数据：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- 查询"电子产品"分类下的所有子分类及商品数量

WITH RECURSIVE category_tree AS (

    -- 锚定：从"电子产品"分类开始

    SELECT id, name, parent_id, 0 AS depth, name AS full_path

    FROM categories

    WHERE id = 100

    

    UNION ALL

    

    -- 递归：向下遍历子分类

    SELECT c.id, c.name, c.parent_id,

           ct.depth + 1 AS depth,

           CONCAT(ct.full_path, ' > ', c.name) AS full_path

    FROM categories c

    JOIN category_tree ct ON c.parent_id = ct.id

),

-- 统计每个分类（含子分类）的商品数

category_stats AS (

    SELECT ct.id, ct.name, ct.depth, ct.full_path,

           COUNT(p.id) AS product_count

    FROM category_tree ct

    LEFT JOIN products p ON p.category_id = ct.id

    GROUP BY ct.id, ct.name, ct.depth, ct.full_path

)

SELECT * FROM category_stats 

WHERE product_count > 0

ORDER BY depth, full_path;

这里我们组合了递归CTE和非递归CTE，先递归展开分类树，再统计每个分类的商品数量，最终得到一个完整的分类统计报表。

向上遍历：查询祖先链

递归CTE不仅能向下遍历子节点，也可以向上遍历祖先链：

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 查询"智能手机"分类的完整祖先路径

WITH RECURSIVE ancestors AS (

    SELECT id, name, parent_id, 0 AS level_from_leaf

    FROM categories

    WHERE id = 205  -- "智能手机"

    

    UNION ALL

    

    SELECT c.id, c.name, c.parent_id, a.level_from_leaf + 1

    FROM categories c

    JOIN ancestors a ON c.id = a.parent_id

)

SELECT * FROM ancestors ORDER BY level_from_leaf DESC;

这在面包屑导航（Breadcrumb）中非常有用——从当前页面回溯到根路径，生成导航层级。

递归CTE的高级应用

生成序列与日期范围

递归CTE可以方便地生成连续的数字序列或日期范围，这在报表和缺失数据填充场景中非常实用：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 生成最近30天的日期序列

WITH RECURSIVE date_series AS (

    SELECT CURDATE() AS dt

    UNION ALL

    SELECT DATE_SUB(dt, INTERVAL 1 DAY)

    FROM date_series

    WHERE dt > DATE_SUB(CURDATE(), INTERVAL 30 DAY)

),

-- 左连接确保每天都有记录（缺失日期填充为0）

daily_stats AS (

    SELECT d.dt, COALESCE(COUNT(o.id), 0) AS order_count

    FROM date_series d

    LEFT JOIN orders o ON DATE(o.created_at) = d.dt

    GROUP BY d.dt

)

SELECT * FROM daily_stats ORDER BY dt;

这个技巧解决了报表中常见的问题：当某天没有数据时，该日期会从结果中消失，导致折线图出现断点。用递归CTE生成完整日期序列再左连接，可以确保每一天都有记录。

路径搜索：最短路径问题

图结构数据（如社交关系、航班路线）也可以用递归CTE处理：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- 查找从城市A到城市B的所有路径（限制最大3次中转）

WITH RECURSIVE routes AS (

    -- 锚定：从起点出发的所有直达航班

    SELECT 

        departure AS from_city,

        arrival AS to_city,

        CAST(CONCAT(departure, '->', arrival) AS CHAR(500)) AS route,

        1 AS stops,

        price AS total_price

    FROM flights

    WHERE departure = '北京'

    

    UNION ALL

    

    -- 递归：在当前路径末尾追加航班

    SELECT 

        r.from_city,

        f.arrival AS to_city,

        CONCAT(r.route, '->', f.arrival) AS route,

        r.stops + 1 AS stops,

        r.total_price + f.price AS total_price

    FROM routes r

    JOIN flights f ON r.to_city = f.departure

    WHERE r.stops < 4  -- 防止无限递归

      AND FIND_IN_SET(f.arrival, REPLACE(r.route, '->', ',')) = 0  -- 避免环路

)

SELECT route, stops, total_price

FROM routes

WHERE to_city = '上海'

ORDER BY stops, total_price

LIMIT 10;

注意

的使用——它检查当前航班的到达城市是否已经出现在路径中，从而避免出现环路（如北京→上海→北京→上海），这是递归路径搜索必须处理的问题。

分摊计算：利润逐级分配

在多层代理体系中，利润需要按比例逐级分配，递归CTE可以优雅地实现这种”瀑布式”计算：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- 多级分销利润分摊

WITH RECURSIVE profit_distribution AS (

    -- 锚定：顶级经销商获得全部利润

    SELECT 

        agent_id,

        parent_id,

        agent_name,

        commission_rate,

        10000.00 AS total_profit,  -- 原始利润

        10000.00 * commission_rate AS own_profit,  -- 本级抽成

        10000.00 * (1 - commission_rate) AS remaining,  -- 剩余分给下级

        0 AS level

    FROM agents WHERE parent_id IS NULL

    

    UNION ALL

    

    -- 递归：上级分完后的剩余利润按比例分给下级

    SELECT 

        a.agent_id, a.parent_id, a.agent_name, a.commission_rate,

        p.remaining AS total_profit,

        p.remaining * a.commission_rate AS own_profit,

        p.remaining * (1 - a.commission_rate) AS remaining,

        p.level + 1 AS level

    FROM agents a

    JOIN profit_distribution p ON a.parent_id = p.agent_id

)

SELECT agent_name, level, total_profit, own_profit, remaining

FROM profit_distribution

ORDER BY level, agent_name;

递归CTE的性能优化与注意事项

递归深度限制

MySQL 8.0 默认的递归深度限制为1000次迭代。可以通过系统变量调整：

1
2
3
4
5
6
7
8
9
-- 查看当前限制

SHOW VARIABLES LIKE 'cte_max_recursion_depth';



-- 临时调整（会话级别）

SET SESSION cte_max_recursion_depth = 10000;



-- 永久调整（需写入配置文件）

-- [mysqld]

-- cte_max_recursion_depth = 10000

在生产环境中，请根据实际数据深度合理设置此值。设置过大会导致极端情况下查询长时间运行，设置过小则可能截断合法的深层递归结果。

递归CTE的执行计划分析

递归CTE的执行计划在EXPLAIN中有特殊标记：

1
2
3
4
5
6
7
8
9
EXPLAIN FORMAT=TREE

WITH RECURSIVE subordinates AS (

    SELECT id, name, manager_id, 0 AS level

    FROM employees WHERE id = 1

    UNION ALL

    SELECT e.id, e.name, e.manager_id, s.level + 1

    FROM employees e JOIN subordinates s ON e.manager_id = s.id

)

SELECT * FROM subordinates;

你会看到输出中包含

的标记。MySQL 8.0.24+ 版本中，EXPLAIN ANALYZE 还能显示递归的迭代次数，这对于判断递归是否合理非常有用。

常见性能陷阱与优化策略

陷阱1：递归成员缺少索引

递归成员中的JOIN条件必须有索引支撑。例如上面的组织架构查询，

必须建立索引：

1
ALTER TABLE employees ADD INDEX idx_manager_id (manager_id);

没有这个索引，每次递归迭代都会对employees表做全表扫描，在层级较深时性能急剧下降。

陷阱2：递归成员中引用外部大表

递归成员应该尽量只处理少量数据。如果在递归成员中JOIN了一个大表并做复杂过滤，每层递归都会重复这个昂贵操作。优化思路是先在锚定成员或单独CTE中完成过滤，再在递归中使用过滤后的小结果集：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 不好的写法：递归成员中过滤大表

WITH RECURSIVE bad_example AS (

    SELECT id, parent_id FROM large_tree WHERE root_id = 1

    UNION ALL

    SELECT t.id, t.parent_id 

    FROM large_tree t      -- 大表！

    JOIN bad_example b ON t.parent_id = b.id

    WHERE t.status = 'active'  -- 每层都过滤

)



-- 好的写法：先过滤，再递归

WITH filtered_tree AS (

    SELECT id, parent_id FROM large_tree WHERE status = 'active'

),

RECURSIVE good_example AS (

    SELECT id, parent_id FROM filtered_tree WHERE root_id = 1

    UNION ALL

    SELECT f.id, f.parent_id 

    FROM filtered_tree f   -- 小结果集

    JOIN good_example g ON f.parent_id = g.id

)

陷阱3：UNION ALL而非UNION

递归CTE必须使用

，不能用

。

会对每层的结果做去重，这在语义上与递归的逐层扩展机制冲突。如果需要最终去重，可以在最外层使用

。

递归CTE与存储过程的对比

在MySQL 8.0之前，层级遍历通常需要存储过程来实现：

CTE在MySQL 8.0中的限制与兼容性

语法限制

MySQL的CTE实现存在一些限制需要注意：

• 递归CTE的递归成员不能包含

  1

  GROUP BY

  、

  1

  DISTINCT

  、

  1

  ORDER BY

  、

  1

  LIMIT

  、

  1

  聚合函数

  、

  1

  窗口函数

• 递归成员只能引用CTE自身一次（不能自连接）
• CTE定义中不能引用同一WITH子句中后面的CTE（MySQL按定义顺序处理）
• 递归CTE的列类型由锚定成员决定，递归成员的对应列必须兼容

与其他数据库的兼容性

CTE是SQL标准特性，各大数据库的实现略有差异：

如果你的应用需要跨数据库兼容，递归CTE是比Oracle的

或SQL Server的

更通用的选择。

MySQL 8.0 CTE的优化器处理

理解MySQL如何处理CTE对性能调优至关重要：

物化（Materialization）策略： MySQL 8.0默认将CTE结果物化为临时表。这意味着CTE只执行一次，后续引用直接读取临时表。但在某些场景下，物化反而比内联展开更慢，因为临时表没有索引。

MySQL 8.0.16+ 的hint： 可以使用optimizer hint来控制CTE是否物化：

1
2
3
4
5
6
7
8
9
10
11
-- 强制物化（默认行为）

WITH /*+ SET_VAR(optimizer_switch='derived_merge=off') */ 

dept_avg AS (SELECT dept_id, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id)

SELECT * FROM dept_avg;



-- 提示优化器合并CTE（8.0.16+支持）

WITH dept_avg AS (

    SELECT /*+ MERCE */ dept_id, AVG(salary) AS avg_sal 

    FROM employees GROUP BY dept_id

)

SELECT * FROM dept_avg;

对于小结果集的简单CTE，内联合并（merge）通常更快，因为它避免了创建临时表的开销。对于大结果集或被多次引用的CTE，物化更合适。

实战案例：电商订单的完整血缘追踪

最后，我们用一个综合案例展示CTE在真实业务中的威力——追踪订单从创建到完成的全链路：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
WITH RECURSIVE order_trace AS (

    -- 锚定：订单创建事件

    SELECT 

        order_id,

        'created' AS event_type,

        created_at AS event_time,

        user_id,

        NULL AS related_order_id,

        0 AS step

    FROM orders

    WHERE id = 12345

    

    UNION ALL

    

    -- 递归：追踪关联订单（如退款单、换货单）

    SELECT 

        r.order_id,

        r.event_type,

        r.event_time,

        r.user_id,

        r.related_order_id,

        ot.step + 1

    FROM (

        SELECT id AS order_id, 'refund' AS event_type, created_at AS event_time,

               user_id, original_order_id AS related_order_id

        FROM refund_orders

        UNION ALL

        SELECT id AS order_id, 'exchange' AS event_type, created_at AS event_time,

               user_id, original_order_id AS related_order_id

        FROM exchange_orders

    ) r

    JOIN order_trace ot ON r.related_order_id = ot.order_id

    WHERE ot.step < 5  -- 最多追踪5层关联

),

-- 汇总每个订单的状态变更时间线

order_timeline AS (

    SELECT 

        ot.order_id,

        ot.event_type,

        ot.event_time,

        l.status,

        l.changed_at,

        ot.step

    FROM order_trace ot

    LEFT JOIN order_status_log l ON l.order_id = ot.order_id

)

SELECT 

    order_id,

    event_type,

    event_time,

    status,

    changed_at,

    step

FROM order_timeline

ORDER BY step, event_time, changed_at;

这个查询实现了”从源头订单出发，递归追踪所有关联订单（退款、换货），并在每个订单上展开状态变更时间线”的需求。没有递归CTE，这需要多次查询和应用程序端的递归逻辑才能实现。

总结与最佳实践

MySQL 8.0的CTE特性为复杂查询提供了强大的表达工具，以下是我们推荐的最佳实践：

• 优先使用CTE替代深层嵌套子查询——可读性和维护性的提升远大于微小的性能差异
• 递归成员的JOIN条件必须建索引——这是递归CTE性能的生命线
• 设置合理的递归深度限制——防止数据异常导致无限递归
• 在递归成员中避免复杂聚合——将聚合操作放在外层或单独CTE中
• 用path字段追踪路径——既能展示结果，又能检测环路
• 先用小数据集验证逻辑——递归CTE的bug在大数据上才暴露，调试成本高
• EXPLAIN ANALYZE是诊断利器——关注递归迭代次数和每行耗时

掌握CTE和递归查询，你就能用纯SQL优雅地处理树形数据、图遍历、序列生成等过去需要大量应用层代码的场景。这是MySQL 8.0送给每个开发者的强大工具，值得深入学习和实践。