在之前的文章中介紹了關(guān)于窗口函數(shù)的一些知識，在本文中來看一下在MySQL8.0中另一個重要的特性–CTE(公用表表達式)。咱們來看下什么是CTE（公共表表達式）？

一、CTE簡介(公用表表達式)

1.1 什么是CTE(公用表表達式)

CTE(公用表表達式)是一個命名的臨時結(jié)果集，僅在單個SQL語句的執(zhí)行范圍內(nèi)存在。與派生表類似，CTE不作為對象存儲，僅在查詢執(zhí)行期間持續(xù)。與派生表不同，CTE可以是自引用。此外，與派生表相比，CTE提供了更好的可讀性和性能。CTE的結(jié)構(gòu)包括:名稱，可選列列表和定義CTE的查詢。定義CTE后，可以像SELECT，INSERT，UPDATE，DELETE或視圖一樣使用。

1.2 CTE(公用表表達式)功能

CTE有兩種用法，非遞歸的CTE和遞歸的CTE。非遞歸的CTE可以用來增加代碼的可讀性，增加邏輯的結(jié)構(gòu)化表達。遞歸的CTE，應(yīng)用的場景也比較多，比如查詢某結(jié)構(gòu)下的子結(jié)構(gòu)，每個子結(jié)構(gòu)下面的子結(jié)構(gòu)等等，就需要使用遞歸的方式。遞歸的CTE當然遞歸不會無限下去，不同的數(shù)據(jù)庫有不同的遞歸限制，MySQL8.0中默認限制的最大遞歸次數(shù)是1000。超過最大低估次數(shù)會報錯：Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value，由參數(shù)cte_max_recursion_depth決定。

二、CTE(公用表表達式)語法及特點

2.1 CTE(公用表表達式)語法

在包含WITH子句的語句中，可以引用每個CTE名稱以訪問相應(yīng)的CTE結(jié)果集。可以在其他CTE中引用CTE名稱，從而可以基于其他CTE定義CTE。CTE可以引用自身來定義遞歸CTE，遞歸CTE的常見應(yīng)用包括序列生成和遍歷分層或樹狀數(shù)據(jù)。公用表表達式使用WITH子句定義：

with_clause:
    WITH [RECURSIVE]
        cte_name [(col_name [, col_name] ...)] AS (subquery)
        [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...
#cte_name命名單個公用表表達式，并且可以在包含該WITH子句的語句中用作表引用。subquery部分稱為“CTE的子查詢”，是產(chǎn)生CTE結(jié)果集的部分。如果公用表表達式的子查詢引用其自己的名稱，則該表表達式是遞歸的，RECURSIVE關(guān)鍵字必須被包含。

要指定公用表表達式，需使用WITH具有一個或多個逗號分隔子句的子句。每個子句都提供一個子查詢，該子查詢產(chǎn)生一個結(jié)果集，并將一個名稱與該子查詢相關(guān)聯(lián)。下面的示例定義名為cte1和cte2中WITH子句，并且是指在它們的頂層SELECT下面的WITH子句：

WITH
  cte1 AS (SELECT id, amount FROM t1),
  cte2 AS (SELECT id, amount FROM t2)
SELECT cte1.amount, cte2.amount FROM cte1 JOIN cte2
WHERE cte1.id = cte2.id;
#列表中的名稱數(shù)必須與結(jié)果集中的列數(shù)相同

2.2 CTE(公用表表達式)特點

WITH在以下情況下允許使用子句：

在開始時SELECT， UPDATE和 DELETE語句。

WITH ... SELECT ...
WITH ... UPDATE ...
WITH ... DELETE ...

在子查詢（包括派生表子查詢）的開頭：

SELECT ... WHERE id IN (WITH ... SELECT ...) ...
SELECT * FROM (WITH ... SELECT ...) AS dt ...

SELECT 對于包含以下SELECT語句的語句， 緊接在前面：

INSERT ... WITH ... SELECT ...
REPLACE ... WITH ... SELECT ...
CREATE TABLE ... WITH ... SELECT ...
CREATE VIEW ... WITH ... SELECT ...
DECLARE CURSOR ... WITH ... SELECT ...
EXPLAIN ... WITH ... SELECT ...

WITH同一級別 僅允許一個子句。不允許在同一級別WITH后面跟隨WITH，因此這是非法的：

WITH cte1 AS (...) WITH cte2 AS (...) SELECT ...

為了使該語句合法，請使用單個 WITH子句以逗號分隔各子句：

WITH cte1 AS (...), cte2 AS (...) SELECT ...

但是，一個語句可以包含多個 WITH子句（如果它們出現(xiàn)在不同的級別）：

WITH cte1 AS (SELECT 1)
SELECT * FROM (WITH cte2 AS (SELECT 2) SELECT * FROM cte2 JOIN cte1) AS dt;

一個WITH子句可以定義一個或多個公用表表達式，但每個CTE名稱必須是唯一的條款。這是非法的：

WITH cte1 AS (...), cte1 AS (...) SELECT ...

為了使該語句合法，請使用唯一的名稱定義CTE：

WITH cte1 AS (...), cte2 AS (...) SELECT ...

CTE可以引用自身或其他CTE：

• 自引用CTE是遞歸的。
• CTE可以引用先前在同一WITH子句中定義的CTE ，但不能引用稍后定義的CTE 。
  此約束排除了相互遞歸的CTE，其中 cte1引用cte2 和cte2引用 cte1。這些引用之一必須是稍后定義的CTE，這是不允許的。
• 給定查詢塊中的CTE可以引用在更外部級別的查詢塊中定義的CTE，但不能引用在更內(nèi)部級別的查詢塊中定義的CTE。
  為了解析對具有相同名稱的對象的引用，派生表會隱藏CTE。CTE隱藏基本表，TEMPORARY表和視圖。通過在同一查詢塊中搜索對象來進行名稱解析，然后在未找到具有該名稱的對象的情況下依次進入外部塊。

2.3 公用表表達式與類似構(gòu)造的比較

公用表表達式（CTE）在某些方面類似于派生表：

• 兩種結(jié)構(gòu)都被命名。
• 兩種構(gòu)造都存在于單個語句的范圍內(nèi)。

由于這些相似之處，CTE和派生表通?？梢曰Q使用。作為一個簡單的例子，這些語句是等效的：

WITH cte AS (SELECT 1) SELECT * FROM cte;
SELECT * FROM (SELECT 1) AS dt;

但是，CTE與派生表相比具有一些優(yōu)勢：

• 在查詢中只能一次引用派生表。可以多次引用CTE。要使用派生表結(jié)果的多個實例，您必須多次派生結(jié)果。
• CTE可以是自引用的（遞歸的）。
• 一個CTE可以引用另一個。
• 當CTE的定義出現(xiàn)在語句的開始而不是嵌入在語句的開頭時，它可能更易于閱讀。
  CTE與使用創(chuàng)建的表相似，CREATE [TEMPORARY] TABLE但無需顯式定義或刪除。對于CTE，不需要創(chuàng)建表的權(quán)限。

三、遞歸查詢

3.1 遞歸查詢介紹

遞歸CTE子查詢分為兩部分，用UNION [ALL] 或分隔 UNION DISTINCT：

SELECT ...      -- return initial row set
UNION ALL
SELECT ...      -- return additional row sets

第一個SELECT生成CTE的初始行或多個行，并且不引用CTE名稱。第二個SELECT 通過引用其FROM子句中的CTE名稱產(chǎn)生其他行并遞歸。當此部分不產(chǎn)生新行時，遞歸結(jié)束。因此，遞歸CTE由一個非遞歸 SELECT部分和一個遞歸SELECT部分組成。

每個SELECT部分本身可以是多個SELECT 語句的并集。

CTE結(jié)果列的類型SELECT只能從非遞歸部分的列類型中推斷出來 ，并且這些列都是可空的。對于類型確定，將SELECT忽略遞歸部分。

如果非遞歸和遞歸部分之間用分隔UNION DISTINCT，則將 消除重復(fù)的行。這對于執(zhí)行傳遞閉包的查詢很有用，以避免無限循環(huán)。

遞歸部分的每次迭代僅對前一次迭代產(chǎn)生的行進行操作。如果遞歸部分具有多個查詢塊，則每個查詢塊的迭代將以未指定的順序進行調(diào)度，并且每個查詢塊將對從上一次迭代結(jié)束后由其上一次迭代或其他查詢塊生成的行進行操作。

3.2 遞歸查詢示例

MySQL [test]> WITH RECURSIVE cte (n) AS ( ? SELECT 1 ? UNION ALL ? SELECT n + 1 FROM cte WHERE n < 10 ) SELECT * FROM cte;
+------+
| n ? ?|
+------+
| ? ?1 |
| ? ?2 |
| ? ?3 |
| ? ?4 |
| ? ?5 |
| ? ?6 |
| ? ?7 |
| ? ?8 |
| ? ?9 |
| ? 10 |
+------+
10 rows in set (0.00 sec)
MySQL [test]> WITH RECURSIVE cte (n) AS ( ? SELECT 1 ? UNION ALL ? SELECT n + 1 FROM cte WHERE n < 100000 ) SELECT * FROM cte;
ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.

3.3 遞歸查詢特點

遞歸SELECT部分不得包含以下構(gòu)造：

• 聚合函數(shù)，如SUM()
• 窗口函數(shù)
• GROUP BY
• ORDER BY
• LIMIT
• DISTINCT

此約束不適用于SELECT遞歸CTE 的非遞歸 部分。禁止DISTINCT僅適用于UNION會員； UNION DISTINCT被允許。

遞歸SELECT部分必須僅在其FROM子句中引用一次CTE ，而不能在任何子查詢中引用 。它可以引用CTE以外的表，并將它們與CTE聯(lián)接在一起。如果在這樣的聯(lián)接中使用，則CTE不得位于的右側(cè)LEFT JOIN。

這些約束來自于SQL標準，比其他的MySQL特定的排除ORDER BY，LIMIT和DISTINCT。

遞歸的CTE，EXPLAIN 遞歸輸出行SELECT 部件顯示Recursive在 Extra列中。

顯示的成本估算值 EXPLAIN代表每次迭代的成本，可能與總成本有很大不同。優(yōu)化器無法預(yù)測迭代次數(shù)，因為它無法預(yù)測該WHERE子句何時變?yōu)榧佟?/p>

CTE實際成本也可能會受到結(jié)果集大小的影響。產(chǎn)生許多行的CTE可能需要一個內(nèi)部臨時表，該表必須足夠大才能從內(nèi)存格式轉(zhuǎn)換為磁盤格式，并且可能會降低性能。如果是這樣，則增加允許的內(nèi)存中臨時表大小可能會提高性能；請參見第8.4.4節(jié)“ MySQL中的內(nèi)部臨時表使用”。

對于遞歸CTE，重要的是遞歸 SELECT部分包括終止遞歸的條件。作為一種防止遞歸CTE失控的開發(fā)技術(shù)，您可以通過限制執(zhí)行時間來強制終止：

該cte_max_recursion_depth 系統(tǒng)變量強制對CTE的遞歸水平的數(shù)量限制。服務(wù)器終止任何遞歸級別高于此變量值的CTE的執(zhí)行。

所述max_execution_time 系統(tǒng)變量強制用于執(zhí)行超時 SELECT在當前會話中執(zhí)行的語句。

該MAX_EXECUTION_TIME 優(yōu)化器提示強制為每個查詢執(zhí)行超時SELECT在它出現(xiàn)的語句。

假設(shè)在沒有遞歸執(zhí)行終止條件的情況下錯誤地編寫了遞歸CTE：

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte
)
SELECT * FROM cte;

默認情況下，cte_max_recursion_depth值為1000，導(dǎo)致CTE遞歸超過1000級時終止。應(yīng)用程序可以更改會話值以適應(yīng)其要求：

SET SESSION cte_max_recursion_depth = 10;      -- permit only shallow recursion
SET SESSION cte_max_recursion_depth = 1000000; -- permit deeper recursion

您還可以設(shè)置全局 cte_max_recursion_depth值以影響隨后開始的所有會話。
對于執(zhí)行緩慢并因此遞歸的查詢，或者在有理由將該cte_max_recursion_depth值設(shè)置得很高的上下文中 ，另一種防止深度遞歸的方法是設(shè)置每個會話超時。為此，請在執(zhí)行CTE語句之前執(zhí)行如下語句：

SET max_execution_time = 1000; -- impose one second timeout

或者，在CTE語句本身中包含優(yōu)化程序提示：

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte
)
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM cte;

如果沒有執(zhí)行時間限制的遞歸查詢進入無限循環(huán)，則可以使用終止另一個會話的查詢 KILL QUERY。在會話本身內(nèi)，用于運行查詢的客戶端程序可能提供一種殺死查詢的方法。

四、總結(jié)

窗口函數(shù)和CTE(公用表表達式)的增加，簡化了SQL代碼的編寫和邏輯的實現(xiàn)，新特性的增加，可以用更優(yōu)雅和可讀性的方式來寫SQL。不過這都是在MySQL8.0中實現(xiàn)的新功能，在MySQL8.0之前，只能按照較為復(fù)雜的方式實現(xiàn)。

到此這篇關(guān)于MySQL8.0之CTE(公用表表達式)的使用的文章就介紹到這了,更多相關(guān)MySQL CTE內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論