加入收藏 | 设为首页 | 会员中心 | 我要投稿 大连站长网 (https://www.0411zz.cn/)- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置: 首页 > 站长学院 > MySql教程 > 正文

MySQL 8.0新特点 CTE

发布时间:2022-07-08 04:16:00 所属栏目:MySql教程 来源:互联网
导读:MySQL 8.0新特点 CTE: 1、CTE简介 MySQL从8.0开始支持CTE,慢慢地向Oracle学习,CTE确实是个很好用的东西,特别是针对OLAP类型的SQL,可以大大简化,优化SQL. 那么什么是CTE呢? 个人理解:CTE(common table expression)是一个临时的结果集,类似一个函数
       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     | <derived2> | NULL       | ref   | <auto_key0>   | <auto_key0> | 4       | testdb.e1.emp_no |     10 |   100.00 | NULL        |
  |  1 | PRIMARY     | <derived2> | NULL       | ref   | <auto_key0>   | <auto_key0> | 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需。

(编辑:大连站长网)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!