第 20 篇 通用表表达式
《MySQL 入门教程》第 20 篇 通用表表达式
上一篇介绍了子查询的各种概念和作用,本篇我们讨论一个和派生表类似但更加强大的功能:通用表表达式。
通用表表达式(Common Table Expression)是一个在语句级别定义的临时结果集,定义之后可以在该语句中多次进行引用。MySQL 8.0 开始支持 CTE,包括简单形式的 CTE 和递归形式的 CTE。
20.1 简单 CTE
CTE 也称为 WITH 子句,它的基本语法如下:
WITH cte_name (col1, col2, ...) AS (
subquery
)
SELECT * FROM cte_name;
其中,WITH 关键字表明这是一个通用表表达式;cte_name 是它的名字,括号内是可选的字段名;AS 之后是它的定义语句;最后在主查询语句中通过名字引用了前面定义的 CTE。
以下语句是上一篇中的派生表示例,用于查找部门信息和对应的员工数量:
select d.dept_name as "部门名称",
coalesce(de.emp_number,0) as "员工数量"
from department d
left join (select dept_id,
count(*) as emp_number
from employee
group by dept_id) de
on (d.dept_id = de.dept_id);
我们可以使用 CTE 将其改写如下:
with de(dept_id, emp_number) AS (
select dept_id,
count(*) as emp_number
from employee
group by dept_id)
select d.dept_name as "部门名称",
coalesce(de.emp_number,0) as "员工数量"
from department d
left join de on (d.dept_id = de.dept_id);
其中,WITH 子句定义了一个临时结果集,名称为 de;AS 关键字指定了 de 的结构和数据,包含了每个部门的编号和员工数量;最后在连接查询的 JOIN 中使用了临时表 de。该语句的结果与上面的示例相同,但是在逻辑上更加清晰。
📝WITH 子句相当于定义了一个变量表达式,表达式的值是一个表,因此称为通用表表达式。CTE 和子查询类似,可以用于 SELECT、INSERT、UPDATE、DELETE 以及 CREATE VIEW 等语句。
一个语句中可以定义多个 CTE,一个 CTE 被定义之后可以多次引用,而且可以被后面定义的其他 CTE 引用。例如:
with t1(n) as (
select 2
),
t2(m) as (
select n + 1
from t1
)
select t1.n, t2.m, t1.n * t2.m
from t1, t2;
n|m|t1.n * t2.m|
-|-|-----------|
2|3| 6|
以上示例中定义了 2 个 CTE;第一个 CTE 名称为 t1,包含了一条记录;第二个 CTE 名称为 t2,引用 t1 生成了一条记录;每个 CTE 之间使用逗号进行分隔;最后的 SELECT 语句使用前面定义的 2 个 CTE 进行连接查询。
在编程语言中,通常会定义一些变量和函数(方法);变量可以被重复使用,函数可以将代码模块化并且提高程序的可读性与可维护性。与此类似,MySQL 中的通用表表达式能够简化复杂的连接查询和子查询,同时实现查询结果的重复利用,从而提高复杂查询语句的可读性和性能。
20.2 递归 CTE
简单 CTE 可以将 SQL 语句进行模块化,便于阅读和理解;而递归形式的 CTE 可以对自己进行引用,从而非常方便地遍历具有层次结构或者树状结构的数据,例如组织结构和航班中转信息查询。
例如,以下语句使用递归 CTE 生成了一个 1 到 10 的数字序列:
with recursive t(n) as
(
select 1
union all
select n + 1 from t where n < 10
)
select n from t;
n |
--|
1|
2|
3|
4|
5|
6|
7|
8|
9|
10|
其中,RECURSIVE 关键字表示递归;递归 CTE 包含两部分,UNION ALL 中的第一个查询语句用于生成初始化数据,第二个查询语句引用了 CTE 自身。该语句的执行过程如下:
- 运行初始化语句,生成数字 1;
- 第 1 次运行递归部分,此时 n 等于 1,满足查询条件 n < 10,返回数字 2( n+1 );
- 第 2 次运行递归部分,此时 n 等于 2,满足查询条件 n < 10,返回数字 3( n+1 );
- 第 9 次运行递归部分,此时 n 等于 9,满足查询条件 n < 10,返回数字 10( n+1 );
- 第 10 次运行递归部分,此时 n 等于 10;由于不满足查询条件 n < 10,不返回任何结果并且结束递归;
- 最后的查询语句返回 t 中的全部数据,也就是一个 1 到 10 的数字序列。
📝递归 CTE 非常合适用于生成具有某种规律的数字序列,例如斐波那契数列(Fibonacci series),更多示例可以参考这篇文章。
员工表中存储了员工的各种信息,包括员工编号、姓名以及员工经理的编号。公司的老板“刘备”没有上级,对应的经理为空。该公司的组织结构图如下:
以下语句利用递归 CTE 生成了一个组织结构图,显示每个员工的从上到下的管理路径:
with recursive employee_path (emp_id, emp_name, path) as
(
select emp_id, emp_name, cast(emp_name as char(1000)) as path
from employee
where manager is null
union all
select e.emp_id, e.emp_name, cast(concat(ep.path, '->', e.emp_name) as char(1000))
from employee_path ep
join employee e on ep.emp_id = e.manager
)
select *
from employee_path
order by emp_id;
其中,employee_path 是一个递归 CTE;其中的初始化部分用于查找上级经理为空的员工,也就是公司的老板:
select emp_id, emp_name, cast(emp_name as char(1000)) as path
from employee
where manager is null;
emp_id|emp_name|path|
------|--------|----|
1|刘备 |刘备 |
“刘备”是公司的老板。然后第一次执行递归部分,将初始化的结果(employee_path)与员工表进行连接查询,找出“刘备”的所有直接下属员工。返回的结果如下:
emp_id|emp_name |path |
------|---------|-----------|
1|刘备 |刘备 |
2|关羽 |刘备->关羽 |
3|张飞 |刘备->张飞 |
4|诸葛亮 |刘备->诸葛亮|
7|孙尚香 |刘备->孙尚香|
9|赵云 |刘备->赵云 |
其中 CONCAT 连接函数用于将之前的管理路径加上当前员工的姓名,生成新的管理路径。不断执行该过程继续返回其他的员工,直到不再返回新的员工为止,最终的返回结果如下:
emp_id|emp_name |path |
------|---------|-------------------|
1|刘备 |刘备 |
2|关羽 |刘备->关羽 |
3|张飞 |刘备->张飞 |
4|诸葛亮 |刘备->诸葛亮 |
5|黄忠 |刘备->诸葛亮->黄忠 |
6|魏延 |刘备->诸葛亮->魏延 |
...
25|孙乾 |刘备->关羽->法正->孙乾|
20.3 递归限制
通常来说,递归 CTE 的定义中需要包含一个终止递归的条件;否则的话,递归将会进入死循环。递归终止的条件可以是遍历完表中的所有数据,不再返回结果;或者是一个 WHERE 终止条件。
以下语句删除了上文生成数字序列的示例中的 WHERE 终止条件:
with recursive t(n) as
(
select 1
union all
select n + 1 from t -- where n < 10
)
select n from t;
ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
默认情况下,MySQL 递归 1000 次后返回错误。这一次数限制由系统变量 cte_max_recursion_depth 进行控制;如果 CTE 递归的次数超过了该变量的值,服务器将会强制终止语句的执行。
cte_max_recursion_depth 可以在会话级别或者全局级别进行设置。例如:
set session cte_max_recursion_depth = 1000000;
set global cte_max_recursion_depth = 1000000;
另外,也可以在 CTE 语句中使用优化器提示:
with recursive t(n) as
(
select 1
union all
select n + 1 from t where n < 10000
)
select /*+ SET_VAR(cte_max_recursion_depth = 1M) */ n from t;
除了递归次数的限制之外,递归 CTE 的递归部分(UNION 之后的 SELECT 语句)不允许出现以下内容:
- 聚合函数,例如 SUM 等;
- 窗口函数;
- GROUP BY;
- ORDER BY;
- DISTINCT。
另外,递归部分只能引用 CTE 名称一次,而且只能在 FROM 子句中(不能在子查询中)引用;如果在递归部分使用其他表和 CTE 进行连接查询, CTE 不能出现在 LEFT JOIN 的右侧。