幹貨 | 解讀MySQL 8.0新特性：CTE

作者：阿裡雲資料庫産品事業部

進階技術專家 印風

前言

CTE也就是common table expressions，是SQL标準裡的文法，很多資料庫都能夠支援，MySQL也在8.0版本裡加入了CTE功能。本文主要簡單的介紹下該文法的用法，由于筆者對server層了解不深，本文不探讨代碼層。

CTE與derived table最大的不同之處是

可以自引用，遞歸使用（recursive cte

在語句級别生成獨立的臨時表. 多次調用隻會執行一次

一個cte可以引用另外一個cte

一個CTE語句其實和CREATE [TEMPORARY] TABLE類似，但不需要顯式的建立或删除，也不需要建立表的權限。更準确的說，CTE更像是一個臨時的VIEW

示例

文法：

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

一條語句裡可以建立多個cte，用逗号隔開:

WITH cta1 AS (SELECT sum(k) from sbtest1 where id < 100) ,
           cta2 AS (SELECT SUM(k) from sbtest2 WHERE id < 100)
SELECT * FROM cta1 JOIN cta2 ;
+----------+----------+
| sum(k)   | SUM(k)   |
+----------+----------+
| 49529621 | 49840812 |
+----------+----------+
1 row in set (0.00 sec)
           

遞歸CTE示例：

root@sb1 09:41:34>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.00 sec)           

遞歸CTE需要加RECURSIVE關鍵字，使用Union all來産生結果

SELECT ...定義初始化值，不引用自身, 同時初始化值的列也定義了cte上的列的個數和類型，可以用cast重定義
UNION ALL
SELECT ....傳回更多的值，并定義退出循環條件，這裡引用了cte自身

其實作類似于：

- non-recursive query block is evaluated, result goes into an internal tmp table
- if no rows, exit
- (A): recursive query block is evaluated over the tmp table's lastly inserted
rows, and it produces new rows which are appended to the tmp table (if UNION
ALL; only distinct not-already-there rows if UNION DISTINCT)
- if the last step didn't produce new rows, exit
- goto (A)           

遞歸的部分不可以包含:

Aggregate functions such as SUM()
Window functions
GROUP BY
ORDER BY
LIMIT
DISTINCT           

再舉個典型的斐波拉契數 (Fibonacci Series Generation)

WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS
(
  SELECT 1, 0, 1
  UNION ALL
  SELECT n + 1, next_fib_n, fib_n + next_fib_n
    FROM fibonacci WHERE n < 10
)
SELECT * FROM fibonacci;

+------+-------+------------+
| n    | fib_n | next_fib_n |
+------+-------+------------+
|    1 |     0 |          1 |
|    2 |     1 |          1 |
|    3 |     1 |          2 |
|    4 |     2 |          3 |
|    5 |     3 |          5 |
|    6 |     5 |          8 |
|    7 |     8 |         13 |
|    8 |    13 |         21 |
|    9 |    21 |         34 |
|   10 |    34 |         55 |
+------+-------+------------+
10 rows in set (0.00 sec)           

關于遞歸的深度，除了自定義推出條件外，為了避免無限遞歸，也定義了一個系統參數cte_max_recursion_depth來限制深度，預設值為1000:

（延伸閱讀：

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_cte_max_recursion_depth

）

root@sb1 09:53:31>SELECT @@SESSION.cte_max_recursion_depth;
+-----------------------------------+
| @@SESSION.cte_max_recursion_depth |
+-----------------------------------+
|                              1000 |
+-----------------------------------+
1 row in set (0.01 sec)

root@sb1 09:53:42>WITH RECURSIVE cte (n) AS (   SELECT 1   UNION ALL   SELECT n + 1 FROM cte WHERE n  < 1001) SELECT * FROM cte;
ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.           

如何實作

筆者對Server層代碼了解不多，這裡隻做簡單的記錄

主要送出的代碼：

https://github.com/mysql/mysql-server/commit/4880f977236b5a33acc531bf420d503f9832781b

參考文檔

官方文檔：

https://dev.mysql.com/doc/refman/8.0/en/with.html

A Definitive Guide To MySQL Recursive CTE：

http://www.mysqltutorial.org/mysql-recursive-cte/

An Introduction to MySQL CTE：

http://www.mysqltutorial.org/mysql-cte/

MySQL | Recursive CTE (Common Table Expressions)：

https://www.geeksforgeeks.org/mysql-recursive-cte-common-table-expressions/