MySQL 8.0新特性--CTE(一)

1、CTE简介

MySQL从8.0开始支持CTE，慢慢地向Oracle学习，CTE确实是个很好用的东西，特别是针对OLAP类型的SQL,可以大大简化，优化SQL.

那么什么是CTE呢？

个人理解：CTE(common table expression)是一个临时的结果集，类似一个函数，一旦定义好，可以多次调用。

2、CTE语法

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

(1)列别名可以在不同的位置定义

mysql> WITH cte (col1, col2) AS    -> (    ->   SELECT 1, 2    ->   UNION ALL    ->   SELECT 3, 4    -> )    -> SELECT col1, col2 FROM cte;+------+------+| col1 | col2 |+------+------+|    1 |    2 ||    3 |    4 |+------+------+2 rows in set (0.00 sec)等价与：mysql> WITH cte AS    -> (    ->   SELECT 1 AS col1, 2 AS col2    ->   UNION ALL    ->   SELECT 3, 4    -> )    -> SELECT col1, col2 FROM cte;+------+------+| col1 | col2 |+------+------+|    1 |    2 ||    3 |    4 |+------+------+2 rows in set (0.00 sec)

(2) CTE用在Select操作

mysql> create table t1(a int,b int);mysql> insert into t1 values(1,1),(2,2),(3,3);mysql> with t as       (select a+2 c,b from t1)        select c,b from t;+------+------+| c    | b    |+------+------+|    3 |    1 ||    4 |    2 ||    5 |    3 |+------+------+3 rows in set (0.00 sec)

(3)CTE用在DML操作

mysql> with t as (select a+2 as a,b from t1) update t1,t set t1.a=t.a+10 where t1.a=t.a;mysql> select * from t1;+------+------+| a    | b    |+------+------+|    1 |    1 ||    2 |    2 ||   13 |    3 |+------+------+3 rows in set (0.00 sec)mysql> with t as (select a+2 as a,b from t1) delete t1 from t1,t where t1.a=t.a;mysql> select * from t1;+------+------+| a    | b    |+------+------+|    1 |    1 ||    2 |    2 |+------+------+2 rows in set (0.00 sec)mysql> insert into t1 with t as (select 10*a as a,b from t1) select * from t;mysql> select * from t1;+------+------+| a    | b    |+------+------+|    1 |    1 ||    2 |    2 ||    3 |    3 ||   10 |    1 ||   20 |    2 ||   30 |    3 |+------+------+6 rows in set (0.00 sec)

3、CTE可以优化SQL

(1)下面第一条SQL可以改写成如下两种CTE简化形式

mysql> select count(*) from employees e1        left join (select * from employees) e2 on e1.emp_no=e2.emp_no        left join (select * from employees) e3 on e2.emp_no=e3.emp_no;       mysql> with e2 as (select * from employees),            e3 as (select * from employees)             select count(*) from employees e1             left join e2 on e1.emp_no=e2.emp_no             left join e3 on e2.emp_no=e3.emp_no;            mysql> with e as(select * from employees) select count(*) from employees e1        left join e e2 on e1.emp_no=e2.emp_no        left join e e3 on e2.emp_no=e3.emp_no;

(2)CTE的本质是子查询，所以子查询的一些特性都适用，如子查询合并。

mysql> desc with e as(select /*+ set_var(optimizer_switch='derived_merge=off')*/ * from employees)    -> select count(*) from employees e1    -> left join e e2 on e1.emp_no=e2.emp_no    -> left join e e3 on e2.emp_no=e3.emp_no;+----+-------------+------------+------------+-------+---------------+-------------+---------+------------------+--------+----------+-------------+| id | select_type | table      | partitions | type  | possible_keys | key         | key_len | ref              | rows   | filtered | Extra       |+----+-------------+------------+------------+-------+---------------+-------------+---------+------------------+--------+----------+-------------+|  1 | PRIMARY     | e1         | NULL       | index | NULL          | PRIMARY     | 4       | NULL             | 299512 |   100.00 | Using index ||  1 | PRIMARY     |  | NULL       | ref   |    |  | 4       | testdb.e1.emp_no |     10 |   100.00 | NULL        ||  1 | PRIMARY     |  | NULL       | ref   |    |  | 4       | e2.emp_no        |     10 |   100.00 | NULL        ||  2 | DERIVED     | employees  | NULL       | ALL   | NULL          | NULL        | NULL    | NULL             | 299512 |   100.00 | NULL        |+----+-------------+------------+------------+-------+---------------+-------------+---------+------------------+--------+----------+-------------+4 rows in set, 2 warnings (0.00 sec)

(3)CTE可以起到减少插入临时表数据，优化SQL的作用

mysql> flush status;Query OK, 0 rows affected (0.02 sec)mysql> select /*+ set_var(optimizer_switch='derived_merge=off')*/ * from    -> (select * from t_group) t1    -> join (select * from t_group) t2    -> on t1.emp_no=t2.emp_no;+--------+---------+------------+------------+--------+---------+------------+------------+| emp_no | dept_no | from_date  | to_date    | emp_no | dept_no | from_date  | to_date    |+--------+---------+------------+------------+--------+---------+------------+------------+|  22744 | d006    | 1986-12-01 | 9999-01-01 |  22744 | d006    | 1986-12-01 | 9999-01-01 ||  24007 | d005    | 1986-12-01 | 9999-01-01 |  24007 | d005    | 1986-12-01 | 9999-01-01 ||  30970 | d005    | 1986-12-01 | 2017-03-29 |  30970 | d005    | 1986-12-01 | 2017-03-29 ||  31112 | d002    | 1986-12-01 | 1993-12-10 |  31112 | d002    | 1986-12-01 | 1993-12-10 ||  40983 | d005    | 1986-12-01 | 9999-01-01 |  40983 | d005    | 1986-12-01 | 9999-01-01 ||  46554 | d008    | 1986-12-01 | 1992-05-27 |  46554 | d008    | 1986-12-01 | 1992-05-27 ||  48317 | d008    | 1986-12-01 | 1989-01-11 |  48317 | d008    | 1986-12-01 | 1989-01-11 ||  49667 | d007    | 1986-12-01 | 9999-01-01 |  49667 | d007    | 1986-12-01 | 9999-01-01 ||  50449 | d005    | 1986-12-01 | 9999-01-01 |  50449 | d005    | 1986-12-01 | 9999-01-01 ||  10004 | d004    | 1986-12-01 | 9999-01-01 |  10004 | d004    | 1986-12-01 | 9999-01-01 |+--------+---------+------------+------------+--------+---------+------------+------------+10 rows in set (0.00 sec)mysql> show status like '%handler_write%';+---------------+-------+| Variable_name | Value |+---------------+-------+| Handler_write | 20    |+---------------+-------+1 row in set (0.00 sec)mysql> flush status;Query OK, 0 rows affected (0.02 sec)mysql> with t as(select /*+ set_var(optimizer_switch='derived_merge=off')*/ * from t_group)    -> select * from t t1    -> join t t2 on t1.emp_no=t2.emp_no;+--------+---------+------------+------------+--------+---------+------------+------------+| emp_no | dept_no | from_date  | to_date    | emp_no | dept_no | from_date  | to_date    |+--------+---------+------------+------------+--------+---------+------------+------------+|  22744 | d006    | 1986-12-01 | 9999-01-01 |  22744 | d006    | 1986-12-01 | 9999-01-01 ||  24007 | d005    | 1986-12-01 | 9999-01-01 |  24007 | d005    | 1986-12-01 | 9999-01-01 ||  30970 | d005    | 1986-12-01 | 2017-03-29 |  30970 | d005    | 1986-12-01 | 2017-03-29 ||  31112 | d002    | 1986-12-01 | 1993-12-10 |  31112 | d002    | 1986-12-01 | 1993-12-10 ||  40983 | d005    | 1986-12-01 | 9999-01-01 |  40983 | d005    | 1986-12-01 | 9999-01-01 ||  46554 | d008    | 1986-12-01 | 1992-05-27 |  46554 | d008    | 1986-12-01 | 1992-05-27 ||  48317 | d008    | 1986-12-01 | 1989-01-11 |  48317 | d008    | 1986-12-01 | 1989-01-11 ||  49667 | d007    | 1986-12-01 | 9999-01-01 |  49667 | d007    | 1986-12-01 | 9999-01-01 ||  50449 | d005    | 1986-12-01 | 9999-01-01 |  50449 | d005    | 1986-12-01 | 9999-01-01 ||  10004 | d004    | 1986-12-01 | 9999-01-01 |  10004 | d004    | 1986-12-01 | 9999-01-01 |+--------+---------+------------+------------+--------+---------+------------+------------+10 rows in set, 1 warning (0.00 sec)mysql> show status like '%handler_write%';+---------------+-------+| Variable_name | Value |+---------------+-------+| Handler_write | 10    |+---------------+-------+1 row in set (0.00 sec)

CTE除了一般功能外,还可以实现递归一些复杂SQL需求，参考MySQL 8.0新特性--CTE(二)

参考链接

13.2.13 WITH Syntax (Common Table Expressions)