MySQL CTE (Common Table Expressions) 详解

CTE (Common Table Expression，公共表表达式) 是 MySQL 8.0 引入的重要特性，它允许在查询中创建临时命名结果集，提高复杂查询的可读性和可维护性。

---

基本语法

WITH cte_name AS (
    SELECT ...  -- CTE查询定义
)
SELECT * FROM cte_name;  -- 主查询

CTE 主要特点

1. 临时结果集：只在查询执行期间存在

2. 可引用性：可以在主查询中多次引用

3. 可读性强：比嵌套子查询更易理解

4. 递归支持：支持递归查询（MySQL 8.0+）

非递归 CTE

简单 CTE 示例

WITH department_stats AS (
    SELECT 
        department_id, 
        COUNT(*) as employee_count,
        AVG(salary) as avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT 
    d.department_name,
    ds.employee_count,
    ds.avg_salary
FROM departments d
JOIN department_stats ds ON d.department_id = ds.department_id;

多 CTE 示例

WITH 
high_earners AS (
    SELECT * FROM employees WHERE salary > 100000
),
it_employees AS (
    SELECT * FROM employees WHERE department_id = 10
)
SELECT 
    h.employee_id,
    h.name,
    'High Earner' as category
FROM high_earners h
UNION ALL
SELECT 
    i.employee_id,
    i.name,
    'IT Employee' as category
FROM it_employees i;

递归 CTE

递归 CTE 可以处理层次结构数据，如组织结构、评论树等。

基本递归 CTE 结构

WITH RECURSIVE cte_name AS (
    -- 基础部分（种子查询）
    SELECT ... WHERE ...
    
    UNION [ALL]
    
    -- 递归部分
    SELECT ... FROM cte_name JOIN ...
    WHERE ...
)
SELECT * FROM cte_name;

递归 CTE 示例：组织结构查询

WITH RECURSIVE org_hierarchy AS (
    -- 基础部分：查找顶级管理者
    SELECT 
        employee_id,
        name,
        manager_id,
        1 as level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 递归部分：查找下属员工
    SELECT 
        e.employee_id,
        e.name,
        e.manager_id,
        oh.level + 1
    FROM employees e
    JOIN org_hierarchy oh ON e.manager_id = oh.employee_id
)
SELECT * FROM org_hierarchy ORDER BY level, employee_id;

递归 CTE 示例：生成序列

WITH RECURSIVE number_sequence AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM number_sequence WHERE n < 10
)
SELECT * FROM number_sequence;

CTE 的优势

1. 提高可读性：将复杂查询分解为逻辑块

2. 避免重复：可以多次引用同一个CTE

3. 替代视图：不需要创建永久视图

4. 递归能力：处理层次结构数据

5. 更好的优化：MySQL优化器能更好处理CTE

CTE 与派生表的比较

特性	CTE	派生表
可读性	高	低
可重用性	可在查询中多次引用	每次使用都需要重新定义
递归支持	支持	不支持
性能	通常更好	可能较差
语法清晰度	更清晰	嵌套较深时难以理解

---

实际应用场景

1. 数据报表：构建复杂报表的多步数据处理

   WITH 
   monthly_sales AS (
       SELECT 
           DATE_FORMAT(order_date, '%Y-%m') as month,
           SUM(amount) as total_sales
       FROM orders
       GROUP BY month
   ),
   growth_rate AS (
       SELECT 
           month,
           total_sales,
           LAG(total_sales) OVER (ORDER BY month) as prev_sales,
           (total_sales - LAG(total_sales) OVER (ORDER BY month)) / 
           LAG(total_sales) OVER (ORDER BY month) * 100 as growth_pct
       FROM monthly_sales
   )
   SELECT * FROM growth_rate;

2. 数据清洗：多步数据转换

   WITH 
   raw_data AS (
       SELECT * FROM source_table WHERE quality_check = 1
   ),
   cleaned_data AS (
       SELECT 
           id,
           TRIM(name) as name,
           CASE WHEN age < 0 THEN NULL ELSE age END as age
       FROM raw_data
   )
   SELECT * FROM cleaned_data;

3. 路径查找：图数据查询

   WITH RECURSIVE path_finder AS (
       SELECT 
           start_node as path,
           start_node,
           end_node,
           1 as length
       FROM graph
       WHERE start_node = 'A'
       
       UNION ALL
       
       SELECT 
           CONCAT(pf.path, '->', g.end_node),
           g.start_node,
           g.end_node,
           pf.length + 1
       FROM graph g
       JOIN path_finder pf ON g.start_node = pf.end_node
       WHERE FIND_IN_SET(g.end_node, REPLACE(pf.path, '->', ',')) = 0
   )
   SELECT * FROM path_finder;

---

性能考虑

1. 物化：MySQL可能会物化CTE结果

2. 递归深度：默认递归深度限制为1000，可通过cte_max_recursion_depth参数调整

   SET SESSION cte_max_recursion_depth = 2000;

3. 优化器提示：可以使用提示影响CTE处理

   WITH cte_name AS (
       SELECT /*+ MERGE() */ * FROM table_name
   )
   SELECT * FROM cte_name;

限制

1. MySQL 8.0 之前版本不支持CTE

2. 某些复杂递归查询可能有性能问题

3. 在存储过程和函数中使用有限制

CTE是MySQL中处理复杂查询的强大工具，合理使用可以显著提高SQL代码的可读性和维护性。