cte (common table expression，公共表表达式) 是 mysql 8.0 引入的重要特性，它允许在查询中创建临时命名结果集，提高复杂查询的可读性和可维护性。
基本语法
with cte_name as (
select ... -- cte查询定义
)
select * from cte_name; -- 主查询cte 主要特点
- 临时结果集：只在查询执行期间存在
- 可引用性：可以在主查询中多次引用
- 可读性强：比嵌套子查询更易理解
- 递归支持：支持递归查询（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 的优势
- 提高可读性：将复杂查询分解为逻辑块
- 避免重复：可以多次引用同一个cte
- 替代视图：不需要创建永久视图
- 递归能力：处理层次结构数据
- 更好的优化：mysql优化器能更好处理cte
cte 与派生表的比较
| 特性 | cte | 派生表 |
|---|---|---|
| 可读性 | 高 | 低 |
| 可重用性 | 可在查询中多次引用 | 每次使用都需要重新定义 |
| 递归支持 | 支持 | 不支持 |
| 性能 | 通常更好 | 可能较差 |
| 语法清晰度 | 更清晰 | 嵌套较深时难以理解 |
实际应用场景
- 数据报表：构建复杂报表的多步数据处理
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;- 数据清洗：多步数据转换
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;- 路径查找：图数据查询
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;性能考虑
- 物化：mysql可能会物化cte结果
- 递归深度：默认递归深度限制为1000，可通过
cte_max_recursion_depth参数调整
set session cte_max_recursion_depth = 2000;
- 优化器提示：可以使用提示影响cte处理
with cte_name as (
select /*+ merge() */ * from table_name
)
select * from cte_name;限制
- mysql 8.0 之前版本不支持cte
- 某些复杂递归查询可能有性能问题
- 在存储过程和函数中使用有限制
cte是mysql中处理复杂查询的强大工具，合理使用可以显著提高sql代码的可读性和维护性。
到此这篇关于mysql cte (common table expressions) 详解的文章就介绍到这了,更多相关mysql cte内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！