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