解读 MySQL 8.0 新特性:CTE

栏目: 数据库 · Mysql · 发布时间: 5年前

内容简介:点击上方蓝字“阿里巴巴数据库技术”关注我们每天都可以接收到阿里云数据库的最新干货!

点击上方蓝字“阿里巴巴数据库技术”关注我们

每天都可以接收到阿里云数据库的最新干货!

解读  <a href='https://www.codercto.com/topics/18746.html'>MySQL</a>  8.0 新特性:CTE

作者: 阿里云数据库产品事业部

高级技术专家 印风

前言

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:

延伸阅读:

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_cte_max_recursion_depth)

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层代码了解不多,这里只做简单的记录

主要提交的代码 :https://github.com/mysql/mysql-server/commit/4880f977236b5a33acc531bf420d503f9832781b

参考文档

官方文档 :https://dev.mysql.com/doc/refman/8.0/en/with.html

A Definitive Guide To MySQL Recursive CTE :http://www.mysqltutorial.org/mysql-recursive-cte/

An Introduction to MySQL CTE :http://www.mysqltutorial.org/mysql-cte/

MySQL | Recursive CTE (Common Table Expressions)

https://www.geeksforgeeks.org/mysql-recursive-cte-common-table-expressions/

过往月报文章

https://yq.aliyun.com/articles/71981


以上所述就是小编给大家介绍的《解读 MySQL 8.0 新特性:CTE》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

海星模式

海星模式

奥瑞·布莱福曼、罗德·贝克斯特朗 / 李江波 / 中信出版社 / 2008-1 / 36.00元

如果砍掉一只蜘蛛的脑袋,毫无疑问它会死掉;但是砍掉海星的一条手臂,它却会长出一只新的来,就连那只砍掉的手臂也会长成一个完整的新海星。传统意义上自上而下的组织模式就像蜘蛛,然而现在正在改变着企业和世界面貌的却是海星型组织。 维基百科、craigslist和Skype的成功下面隐藏着什么样的力量?易趣公司和通用电气公司与废奴和女权运动又有什么共同之处?到底是什么样的重大选择使得通用汽车公司与丰田......一起来看看 《海星模式》 这本书的介绍吧!

URL 编码/解码
URL 编码/解码

URL 编码/解码

html转js在线工具
html转js在线工具

html转js在线工具

HEX CMYK 转换工具
HEX CMYK 转换工具

HEX CMYK 互转工具