MySQL8.0 - 新特性 - CTE(Common Table Expressions)

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云数据库 Redis 版,社区版 2GB
推荐场景:
搭建游戏排行榜
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: 前言CTE也就是common table expressions是sql标准里的语法,很多数据库都能够支持,MySQL也在8.0版本里加入了CTE功能,本文主要简单的介绍下该语法的用法,由于笔者对server层了解不深,本文不探讨代码层CTE与derived table最大的不同之处是可以自引用,递归使用(recursive cte在语句级别生成独立的临时表. 多次调用只会执行一次一个cte可以引用另外一个cte一个CTE语句其实和CREATE [TEMPORARY] TABLE类似,但不需要显式的创建或删除,也不需要创建表的权限。

前言

CTE也就是common table expressions是sql标准里的语法,很多数据库都能够支持,MySQL也在8.0版本里加入了CTE功能,本文主要简单的介绍下该语法的用法,由于笔者对server层了解不深,本文不探讨代码层

CTE与derived table最大的不同之处是

  • 可以自引用,递归使用(recursive cte
  • 在语句级别生成独立的临时表. 多次调用只会执行一次
  • 一个cte可以引用另外一个cte

一个CTE语句其实和CREATE [TEMPORARY] TABLE类似,但不需要显式的创建或删除,也不需要创建表的权限。更准确的说,CTE更像是一个临时的VIEW

示例

语法:

with_clause:
    WITH [RECURSIVE]
        cte_name [(col_name [, col_name] ...)] AS (subquery)
        [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...

一条语句里可以创建多个cte,用逗号隔开:

WITH cta1 AS (SELECT sum(k) from sbtest1 where id < 100) ,  
           cta2 AS (SELECT SUM(k) from sbtest2 WHERE id < 100) 
SELECT * FROM cta1 JOIN cta2 ;
+----------+----------+
| sum(k)   | SUM(k)   |
+----------+----------+
| 49529621 | 49840812 |
+----------+----------+
1 row in set (0.00 sec)

递归CTE示例:

root@sb1 09:41:34>WITH RECURSIVE cte (n) AS
    -> (
    ->   SELECT 1
    ->   UNION ALL
    ->   SELECT n + 1 FROM cte WHERE n < 5
    -> )
    -> SELECT * FROM cte;
+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)

递归CTE需要加RECURSIVE关键字,使用Union all来产生结果

SELECT ...定义初始化值,不引用自身, 同时初始化值的列也定义了cte上的列的个数和类型,可以用cast重定义
UNION ALL
SELECT ....返回更多的值,并定义退出循环条件,这里引用了cte自身

其实现类似于:

- non-recursive query block is evaluated, result goes into an internal tmp table
- if no rows, exit
- (A): recursive query block is evaluated over the tmp table's lastly inserted
rows, and it produces new rows which are appended to the tmp table (if UNION
ALL; only distinct not-already-there rows if UNION DISTINCT)
- if the last step didn't produce new rows, exit
- goto (A)

递归的部分不可以包含:

Aggregate functions such as SUM()
Window functions
GROUP BY
ORDER BY
LIMIT
DISTINCT

再举个典型的斐波拉契数(Fibonacci Series Generation)

WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS
(
  SELECT 1, 0, 1
  UNION ALL
  SELECT n + 1, next_fib_n, fib_n + next_fib_n
    FROM fibonacci WHERE n < 10
)
SELECT * FROM fibonacci;

+------+-------+------------+
| n    | fib_n | next_fib_n |
+------+-------+------------+
|    1 |     0 |          1 |
|    2 |     1 |          1 |
|    3 |     1 |          2 |
|    4 |     2 |          3 |
|    5 |     3 |          5 |
|    6 |     5 |          8 |
|    7 |     8 |         13 |
|    8 |    13 |         21 |
|    9 |    21 |         34 |
|   10 |    34 |         55 |
+------+-------+------------+
10 rows in set (0.00 sec)

关于递归的深度,除了自定义推出条件外,为了避免无限递归,也定义了一个系统参数cte_max_recursion_depth来限制深度,默认值为1000:

root@sb1 09:53:31>SELECT @@SESSION.cte_max_recursion_depth;
+-----------------------------------+
| @@SESSION.cte_max_recursion_depth |
+-----------------------------------+
|                              1000 |
+-----------------------------------+
1 row in set (0.01 sec)

root@sb1 09:53:42>WITH RECURSIVE cte (n) AS (   SELECT 1   UNION ALL   SELECT n + 1 FROM cte WHERE n  < 1001) SELECT * FROM cte;
ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.

如何实现

前文已经说过,笔者对Server层代码了解不多,这里只做简单的记录

主要提交的代码

想看实现思路可以阅读如下两个worklog:
WL#883: Non-recursive WITH clause (Common Table Expression)
WL#3634: Recursive WITH (Common Table Expression)

参考文档

官方文档
A Definitive Guide To MySQL Recursive CTE
An Introduction to MySQL CTE
MySQL | Recursive CTE (Common Table Expressions)
之前的月报文章

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
3月前
|
存储 SQL 关系型数据库
MySQL相关(五)- 事务四大特性及隔离级别的详细介绍
MySQL相关(五)- 事务四大特性及隔离级别的详细介绍
43 0
|
3月前
|
存储 SQL 关系型数据库
MySQL 事务的 ACID 特性
MySQL事务是什么,**它就是一组数据库的操作,是访问数据库的程序单元,事务中可能包含一个或者多个 SQL 语句。这些SQL 语句要么都执行、要么都不执行。**我们知道,在MySQL 中,有不同的存储引擎,有的存储引擎比如MyISAM 是不支持事务的,所以说**MySQL 事务实际上是发生在 存储引擎部分**。
50 0
MySQL 事务的 ACID 特性
|
4月前
|
关系型数据库 MySQL 数据安全/隐私保护
MySQL8.1.0版本正式发布带来哪些新特性?
MySQL8.1.0版本正式发布带来哪些新特性?
225 0
MySQL8.1.0版本正式发布带来哪些新特性?
|
5月前
|
SQL 关系型数据库 MySQL
MySQL5.7 group by新特性报错1055的解决办法
MySQL5.7 group by新特性报错1055的解决办法
|
2月前
|
SQL 关系型数据库 MySQL
Mysql事务隔离级别和锁特性
Mysql事务隔离级别和锁特性
|
4月前
|
SQL 关系型数据库 MySQL
⑨【MySQL事务】事务开启、提交、回滚,事务特性ACID,脏读、幻读、不可重复读。
⑨【MySQL事务】事务开启、提交、回滚,事务特性ACID,脏读、幻读、不可重复读。
32 0
|
4月前
|
存储 SQL 关系型数据库
MySQL8.0新特性与旧特性移除总结
MySQL从5.7版本直接跳跃发布了8.0版本 ,可见这是一个令人兴奋的里程碑版本。MySQL 8版本在功能上做了显著的改进与增强,开发者对MySQL的源代码进行了重构,最突出的一点是对MySQL Optimizer优化器进行了改进。不仅在速度上得到了改善,还为用户带来了更好的性能和更棒的体验。
116 1
|
21天前
|
存储 缓存 关系型数据库
MySQL事务的四大特性是如何保证的
在MySQL数据库中还有一种二进制日志,其用来基于时间点的还原及主从复制。从表面上来看其和重做日志非常相似,都是记录了对于数据库操作的日志。但是,从本质上来看有着非常大的不同。
11 1
|
1月前
|
SQL 关系型数据库 MySQL
深入理解MySQL事务特性:保证数据完整性与一致性
深入理解MySQL事务特性:保证数据完整性与一致性
72 1
|
1月前
|
存储 安全 关系型数据库
MySQL 临时表的用法和特性
MySQL 临时表的用法和特性

相关产品

  • 云数据库 RDS MySQL 版