New feature of MySQL 8.0-- CTE (1)
1. Introduction to CTE
MySQL from 8.0 to support CTE, slowly learn from Oracle, CTE is really a very useful thing, especially for the OLAP type of SQL, can be greatly simplified, optimize SQL.
So what is CTE?
Personal understanding: CTE (common table expression) is a temporary result set, similar to a function, once defined, can be called multiple times.
2. CTE syntax
With_clause: WITH [RECURSIVE] cte_name [(col_name [, col_name]...)] AS (subquery) [, cte_name [(col_name [, col_name]...)] AS (subquery)].
(1) column aliases can be defined in different locations
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) is equivalent to: 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 is used in Select operation
Mysql > create table T1 (an int,b int); mysql > insert into T1 values (1Power1), (2from 2), (3Mague 3); mysql > with t as (select axiom 2c from T1) select c from tkashashi + | c | b | b | +-+ | 3 | 1 | 4 | 2 | 5 | 3 | +-+-+ 3 rows in set (0.00 sec)
(3) CTE is used in DML operation.
Mysql > with t as (select axiom2 as b from T1) update T1 Magi t set t1.a=t.a+10 where t1.aaccount.apolitic MySQL > select * from T1 +-+-+ | a | b | +-+-+ | 1 | 1 | 2 | 2 | 13 | 3 | +-+-+ 3 rows in set (0.00 sec) mysql > with t as (select axiom 2 as from T1) delete from T1 t where T1 t where t 1.aposit 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 years an as a from T1) select * from tbot 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 can optimize SQL.
(1) the first SQL below can be rewritten into the following two simplified forms of 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) the essence of CTE is subquery, so some features of subquery are applicable, such as subquery merging.
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 | 299512 | 100.00 | NULL | +- +-+-+ 4 rows in set 2 warnings (0.00 sec)
(3) CTE can reduce the insertion of temporary table data and optimize 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 | 1986-12-01 | 30,970 | d005 | 1986-12-01 | 2017-12-29 | 31112 | d002 | 1986-12-01 | 1993-12-10 | 31112 | d002 | | 9999-01-01 | 40983 | d005 | 1986-12-01 | 9999-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 | | | 9999-01-01 | | 50449 | d005 | 1986-12-01 | 9999-01-01 | 50449 | d005 | 1986-12-01 | 9999-01-01 | | 10004 | d004 | 1986-12-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 t 1-> join t 2 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 | 1986-12-01 | 30,970 | d005 | 1986-12-01 | 2017-12-29 | 31112 | d002 | 1986-12-01 | 1993-12-10 | 31112 | d002 | | 9999-01-01 | 40983 | d005 | 1986-12-01 | 9999-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 | | | 9999-01-01 | | 50449 | d005 | 1986-12-01 | 9999-01-01 | 50449 | d005 | 1986-12-01 | 9999-01-01 | | 10004 | d004 | 1986-12-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)
In addition to general functions, CTE can also recursively implement some complex SQL requirements. Refer to the new feature of MySQL 8.0-CTE (2).
Reference link
13.2.13 WITH Syntax (Common Table Expressions)
