MySQL8.0新特性CTE(Common Table Expression)

CTE(Common Table Expression)可以認為是派生表(derived table)的替代，在一定程度上，CTE簡化了複雜的join查詢和子查詢，提高了SQL的可讀性和執行性能。CTE是ANSI SQL 99標準的一部分，在MySQL 8.0.1版本被引入。

原文地址：https://mytecdb.com/blogDetail.php?id=75

1. CTE優勢

查詢語句的可讀性更好在一個查詢中，可以被引用多次能夠連接多個CTE能夠創建遞歸查詢能夠提高SQL執行性能能夠有效地替代視圖2. 如何創建和使用CTE

CTE類似於使用子查詢時的派生表，但是CTE的定義不在SQL主體中，而是提到SQL最前端，聲明CTE的需要使用語法WITH。

2.1 CTE的使用

先看一個派生表實現的例子：

使用CTE改寫後，SQL變成這樣：

CTE的語法也比較簡單，在SQL主體查詢之前，使用WITH語法，定義一個或者多個CTE，然後就可以在查詢SQL的主體中引用一次或多次CTE，可以把CTE看成是一類提前物化的臨時表，以便於查詢主體引用。

2.2 為CTE指定具體的欄位名稱

使用圓括號為CTE指定欄位名稱，如下eur_name和eur_population為CTE的欄位：

2.3 CTE也可以被用作數據源來更新其他表

CTE可以作為數據源，來更新或者刪除其他表，如下：

2.4 CTE也可以用於insert ... select 語句

2.5 CTE作為提前物化的臨時表

定義多個CTE，作為提前物化的臨時表，在主查詢裡面可以多次引用這些臨時表。如下：

2.6 CTE的可見性

下面兩個例子，第一個例子中cte對於頂層SELECT可見，第二個例子中，cte對頂層SELECT不可見。為了避免這種不可見的問題，通常將CTE定義在最前面，以便能夠在查詢主體的任何地方都能引用到CTE。

2.7 CTE引用鏈

如果在一個查詢中創建多個CTE，可能會出現一個CTE引用前一個CTE，導致CTE引用鏈的產生。下面這個例子展示了CTE引用鏈：

上述SQL如果使用派生表的方式改寫，也將是非常龐大和複雜的。

2.8 使用CTE代替視圖

如果你的用戶沒有權限創建視圖，而同時又有需要使用視圖，不妨試試CTE來代替視圖。

視圖改寫為CTE，如下：

使用CTE代替視圖能夠有效提高執行效率，在本案例中，視圖的執行時間大概是0.0097秒，而CTE大概是0.0054秒，CTE更快，因為只需要一次物化臨時表，可以被多次引用。

3. 總結

在MySQL 8.0 中引入CTE新特性，在大多數場景下，能夠簡化SQL，提高可讀性，同時也能使用CTE代替視圖，提高整體性能。另外CTE也能實現遞歸查詢，下一篇文章將詳細介紹。

本文譯自：https://www.percona.com/blog/2020/02/10/introduction-to-mysql-8-0-common-table-expressions-part-1/