首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >MySQL 8.0新特性 — CTE(Common Table Expressions)

MySQL 8.0新特性 — CTE(Common Table Expressions)

原创
作者头像
brightdeng@DBA
修改2020-11-10 16:37:59
1.9K0
修改2020-11-10 16:37:59
举报

前言

CTE,Common Table Expressions,是一个非常实用的功能,可以有效降低SQL的编写难度。CTE,也是大多数关系型数据库都支持的,包括DB2、Oracle、SQL Server、PostgreSQL等,唯独MySQL不支持。不过,在MySQL 8.0版本中,终于是补全了该功能,接下来我们就来看一下。

CTE用法

(1)最基本的CTE语法如下

mysql> with 
    -> cte1 as (select * from sbtest1 where id in (1,2)),
    -> cte2 as (select * from sbtest1 where id in (2,3))
    -> select * from cte1 join cte2 where cte1.id=cte2.id;
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k       | c                                                                                                                       | pad                                                         | id | k       | c                                                                                                                       | pad                                                         |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
|  2 | 5934152 | 05879259692-02024081996-76022818191-82933803603-81845875017-31928300264-16934042125-67052432228-92123768050-95121478647 | 79362588344-09017007031-35411714211-52054317597-26283585383 |  2 | 5934152 | 05879259692-02024081996-76022818191-82933803603-81845875017-31928300264-16934042125-67052432228-92123768050-95121478647 | 79362588344-09017007031-35411714211-52054317597-26283585383 |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
1 row in set (0.00 sec)

(2)一个CTE,可以被另一个CTE引用,具体如下:

mysql> with 
    -> cte1 as (select * from sbtest1 where id=1),
    -> cte2 as (select * from cte1)
    -> select * from cte2;
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k       | c                                                                                                                       | pad                                                         |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
|  1 | 3718516 | 08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977 | 63188288836-92351140030-06390587585-66802097351-49282961843 |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
1 row in set (0.00 sec)

(3)一个CTE,还可以被自己引用,具体如下:

mysql> 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.01 sec)

(4)CTE不仅支持select,也支持insert/delete/update

mysql> with cte as
    -> (
    -> select * from sbtest1 where id=1
    -> )
    -> update sbtest1 t,cte set t.c='test',t.pad='test' where t.id=cte.id;

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

CTE与Derived Table

针对from子句里面的subquery,MySQL在不同版本中,是做过一系列的优化,接下来我们就来看看。

(1)在5.6版本中,MySQL会对每一个Derived Table进行物化,生成一个临时表保存Derived Table的结果,然后利用临时表来完成父查询的操作,具体如下:

mysql> explain select * from (select * from sbtest1 where id<=1000) t1 join (select * from sbtest1 where id>=990) t2 on t1.id=t2.id;
+----+-------------+------------+------------+-------+---------------+-------------+---------+-------+---------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key         | key_len | ref   | rows    | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+-------------+---------+-------+---------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL          | NULL        | NULL    | NULL  |    1900 |   100.00 | NULL        |
|  1 | PRIMARY     | <derived3> | NULL       | ref   | <auto_key0>   | <auto_key0> | 4       | t1.id |    2563 |   100.00 | NULL        |
|  3 | DERIVED     | sbtest1    | NULL       | range | PRIMARY       | PRIMARY     | 4       | NULL  | 4870486 |   100.00 | Using where |
|  2 | DERIVED     | sbtest1    | NULL       | range | PRIMARY       | PRIMARY     | 4       | NULL  |    1900 |   100.00 | Using where |
+----+-------------+------------+------------+-------+---------------+-------------+---------+-------+---------+----------+-------------+
4 rows in set, 1 warning (0.00 sec)

(2)在5.7版本中,MySQL引入了Derived Merge新特性,允许符合条件的Derived Table中的子表与父查询的表进行合并,具体如下:

mysql> explain select * from (select * from sbtest1 where id<=1000) t1 join (select * from sbtest1 where id>=990) t2 on t1.id=t2.id;
+----+-------------+---------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------------+
| id | select_type | table   | partitions | type   | possible_keys | key     | key_len | ref             | rows | filtered | Extra       |
+----+-------------+---------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------------+
|  1 | SIMPLE      | sbtest1 | NULL       | range  | PRIMARY       | PRIMARY | 4       | NULL            |   11 |   100.00 | Using where |
|  1 | SIMPLE      | sbtest1 | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test.sbtest1.id |    1 |   100.00 | NULL        |
+----+-------------+---------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

(3)在8.0版本中,我们可以使用CTE实现,其执行计划也是和Derived Table一样

mysql> explain    
    -> with    
    -> t1 as (select * from sbtest1 where id<=1000),    
    -> t2 as (select * from sbtest1 where id>=990)    
    -> select * from t1 join t2 on t1.id=t2.id;
+----+-------------+---------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------------+
| id | select_type | table   | partitions | type   | possible_keys | key     | key_len | ref             | rows | filtered | Extra       |
+----+-------------+---------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------------+
|  1 | SIMPLE      | sbtest1 | NULL       | range  | PRIMARY       | PRIMARY | 4       | NULL            |   11 |   100.00 | Using where |
|  1 | SIMPLE      | sbtest1 | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test.sbtest1.id |    1 |   100.00 | NULL        |
+----+-------------+---------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

从测试结果来看,CTE似乎是Derived Table的一个替代品?其实不是的,虽然CTE内部优化流程与Derived Table类似,但是两者还是区别的,具体如下:

(1)一个CTE可以引用另一个CTE

(2)CTE可以自引用

(3)CTE在语句级别生成临时表,多次调用只需要执行一次,提高性能

总结

从上面介绍可以知道,CTE一方面可以非常方便进行SQL开发,另一方面也可以提升SQL执行效率。

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 前言
  • CTE用法
  • CTE与Derived Table
  • 总结
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档