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

一、CTE簡(jiǎn)介(公用表表達(dá)式)

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

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

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

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

二、CTE(公用表表達(dá)式)語(yǔ)法及特點(diǎn)

2.1 CTE(公用表表達(dá)式)語(yǔ)法

在包含WITH子句的語(yǔ)句中，可以引用每個(gè)CTE名稱(chēng)以訪問(wèn)相應(yīng)的CTE結(jié)果集?？梢栽谄渌鸆TE中引用CTE名稱(chēng)，從而可以基于其他CTE定義CTE。CTE可以引用自身來(lái)定義遞歸CTE，遞歸CTE的常見(jiàn)應(yīng)用包括序列生成和遍歷分層或樹(shù)狀數(shù)據(jù)。公用表表達(dá)式使用WITH子句定義：

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

要指定公用表表達(dá)式，需使用WITH具有一個(gè)或多個(gè)逗號(hào)分隔子句的子句。每個(gè)子句都提供一個(gè)子查詢，該子查詢產(chǎn)生一個(gè)結(jié)果集，并將一個(gè)名稱(chēng)與該子查詢相關(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;
#列表中的名稱(chēng)數(shù)必須與結(jié)果集中的列數(shù)相同

2.2 CTE(公用表表達(dá)式)特點(diǎn)

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

在開(kāi)始時(shí)SELECT， UPDATE和 DELETE語(yǔ)句。

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

在子查詢（包括派生表子查詢）的開(kāi)頭：

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

SELECT 對(duì)于包含以下SELECT語(yǔ)句的語(yǔ)句， 緊接在前面：

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

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

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

為了使該語(yǔ)句合法，請(qǐng)使用單個(gè) WITH子句以逗號(hào)分隔各子句：

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

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

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

一個(gè)WITH子句可以定義一個(gè)或多個(gè)公用表表達(dá)式，但每個(gè)CTE名稱(chēng)必須是唯一的條款。這是非法的：

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

為了使該語(yǔ)句合法，請(qǐng)使用唯一的名稱(chēng)定義CTE：

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

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

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

2.3 公用表表達(dá)式與類(lèi)似構(gòu)造的比較

公用表表達(dá)式（CTE）在某些方面類(lèi)似于派生表：

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

由于這些相似之處，CTE和派生表通常可以互換使用。作為一個(gè)簡(jiǎn)單的例子，這些語(yǔ)句是等效的：

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

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

• 在查詢中只能一次引用派生表?？梢远啻我肅TE。要使用派生表結(jié)果的多個(gè)實(shí)例，您必須多次派生結(jié)果。
• CTE可以是自引用的（遞歸的）。
• 一個(gè)CTE可以引用另一個(gè)。
• 當(dāng)CTE的定義出現(xiàn)在語(yǔ)句的開(kāi)始而不是嵌入在語(yǔ)句的開(kāi)頭時(shí)，它可能更易于閱讀。
  CTE與使用創(chuàng)建的表相似，CREATE [TEMPORARY] TABLE但無(wú)需顯式定義或刪除。對(duì)于CTE，不需要?jiǎng)?chuàng)建表的權(quán)限。

三、遞歸查詢

3.1 遞歸查詢介紹

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

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

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

每個(gè)SELECT部分本身可以是多個(gè)SELECT 語(yǔ)句的并集。

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

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

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

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 遞歸查詢特點(diǎn)

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

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

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

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

這些約束來(lái)自于SQL標(biāo)準(zhǔn)，比其他的MySQL特定的排除ORDER BY，LIMIT和DISTINCT。

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

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

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

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

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

所述max_execution_time 系統(tǒng)變量強(qiáng)制用于執(zhí)行超時(shí) SELECT在當(dāng)前會(huì)話中執(zhí)行的語(yǔ)句。

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

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

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

默認(rèn)情況下，cte_max_recursion_depth值為1000，導(dǎo)致CTE遞歸超過(guò)1000級(jí)時(shí)終止。應(yīng)用程序可以更改會(huì)話值以適應(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值以影響隨后開(kāi)始的所有會(huì)話。
對(duì)于執(zhí)行緩慢并因此遞歸的查詢，或者在有理由將該cte_max_recursion_depth值設(shè)置得很高的上下文中 ，另一種防止深度遞歸的方法是設(shè)置每個(gè)會(huì)話超時(shí)。為此，請(qǐng)?jiān)趫?zhí)行CTE語(yǔ)句之前執(zhí)行如下語(yǔ)句：

SET max_execution_time = 1000; -- impose one second timeout

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

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

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

四、總結(jié)

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

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

最新評(píng)論