MYSQL8.0-Common Table Expressions (CTE)公共表表达式

使用CET派生表的原因

在多个递归的子查询，可以优化使用
对mysql的派生表进行优化，减少递归重复语句
查找多个有规律的select的并集

CET的基础语法

语法结构

WITH RECURSIVE cte_name AS
(
  SELECT ...      <-- specifies initial set
  UNION ALL
  SELECT ...      <-- specifies how to derive new rows
)

举个栗子

USE test;
CREATE TABLE numbers
  WITH RECURSIVE my_cte(n) AS
  (
    SELECT 1
    UNION ALL
    SELECT 1+n FROM my_cte WHERE n<6
  )
  SELECT * FROM my_cte;
Query OK, 6 rows affected (0,40 sec)

SELECT * FROM numbers;
+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
+------+
6 rows in set (0,00 sec

语法规则如下

1.名称是my_cte
2.CTO的定义在SELECT语句之前
3.定义初始数据行
4.初始数据行作为新输入数据行的输入数据
5.通过应用自定以的my_cte就可以判断是否是循环引用。

对于以上的语法规则，我们可以发现以下规则

可以初始换定义多个UNION SELECT 语句
可以将生成行的过程定义为几个SELECT ,结果进行联合
可以定义多个CTE,并且后面的定义的CTE可以使用前面定义的CTE
单个WITH 条件可以混合非循环的CTE和循环的CTE

除了SELECT，CTE还可以用于UPDATE,INSERT,DELETE 等语句的使用

UPDATE 语句的使用中

WITH RECURSIVE my_cte(n) AS
(
  SELECT 1
  UNION ALL
  SELECT 1+n FROM my_cte WHERE n<6
)
UPDATE numbers, my_cte
# Change to 0...
SET numbers.n=0
# ... the numbers which are squares, i.e. 1 and 4
WHERE numbers.n=my_cte.n*my_cte.n;
Query OK, 4 rows affected (0,01 sec)

SELECT * FROM numbers;
+------+
| n    |
+------+
|    0 |
|    2 |
|    3 |
|    0 |
|    5 |
|    6 |
|    0 |
|    2 |
|    3 |
|    0 |
|    5 |
|    6 |
+------+
12 rows in set (0,00 sec)

在INSERT 语句中的使用

INSERT INTO numbers
WITH RECURSIVE my_cte(n) AS
(
  SELECT 1
  UNION ALL
  SELECT 1+n FROM my_cte WHERE n<6
)
SELECT * FROM my_cte;
Query OK, 6 rows affected (0,12 sec)
 
SELECT * FROM numbers;
+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
+------+
12 rows in set (0,00 sec)

在DELETE 语句中的使用

WITH RECURSIVE my_cte(n) AS
(
  SELECT 1
  UNION ALL
  SELECT 1+n FROM my_cte WHERE n<6
)
DELETE FROM numbers
# delete the numbers greater than the average of 1,...,6 (=3.5)
WHERE numbers.n > (SELECT AVG(n) FROM my_cte);
Query OK, 4 rows affected (0,01 sec)

SELECT * FROM numbers;
+------+
| n    |
+------+
|    0 |
|    2 |
|    3 |
|    0 |
|    0 |
|    2 |
|    3 |
|    0 |
+------+
8 rows in set (0,00 sec)

CTE树状结构查询

检索完整树

WITH RECURSIVE cte AS
(
  # seed SELECT
  SELECT category_id, name FROM category WHERE parent IS NULL
  UNION ALL
  # recursive SELECT
  SELECT c.category_id, c.name FROM category c JOIN cte
  ON cte.category_id=c.parent # find children
)
SELECT name FROM cte;
+-------------+----------------------+
| category_id | name                 |
+-------------+----------------------+
|           1 | ELECTRONICS          |
|           2 | TELEVISIONS          |
|           6 | PORTABLE ELECTRONICS |
|           3 | TUBE                 |
|           4 | LCD                  |
|           5 | PLASMA               |
|           7 | MP3 PLAYERS          |
|           9 | CD PLAYERS           |
|          10 | 2 WAY RADIOS         |
|           8 | FLASH                |
+-------------+----------------------+
10 rows in set (0,00 sec)

这个查询执行的过程是这样的：

先查询根节点
根据根节点查询出来的数据作为子查询的输入数据，然后进行递归查询
当递归查询的结果为空的时候，就会退出循环

CTE使用中存在的问题和关注点

使用中存在的问题

1.如果没有设置循环的推出条件（eg:WHERE n<6），将会发生什么？
这个SQL就无法被执行，因为生成的行数超过了MYSQL循环限制的最大值。

怎么知道循环限制的最大值？
mysql中默认的最大循环次数是1000，我们可以通过指令进行查询

show variable '@@cte_max_recursion_depth'

当然也可以自定义这个最大循环数量

SET max_execution_time = 10000;

使用中的关注点

1.如果通过CTE生成一个字符串，那么在定义非循环的的SELECT语句的时候，要注意使用CAST（... AS CHAR()）去定义字符串的长度，因为后续的字符串长度都会以此为基准，可能会出现超长的情况
2.强制性的语法要求：
- 2.1 必须使用UNION DISTINCT 或者 UNION ALL 去关联SELECT语句
- 2.2 循环的SELECT语句中不能包含GROUP BY ,聚合函数，排序，Distinc 之类的操作
- 2.3 循环的SELECT语句只能调用一次定义的CTE表，并且如果存在join操作也只能是LEFT JOIN 这种操作