MySQL 8.4 版本說明
通用表格運算式 (CTE) 是一個命名的暫時結果集，它存在於單一語句的範圍內，並且可以在該語句中稍後引用，可能多次引用。以下討論描述如何編寫使用 CTE 的語句。
關於 CTE 最佳化的資訊，請參閱 第 10.2.2.4 節，「使用合併或實體化最佳化衍生表格、視圖參考和通用表格運算式」。
若要指定通用表格運算式，請使用一個 WITH 子句，其中包含一個或多個逗號分隔的子子句。每個子子句提供一個產生結果集的子查詢，並將一個名稱與該子查詢關聯。以下範例在 WITH 子句中定義名為 cte1 和 cte2 的 CTE，並在 WITH 子句後面的頂層 SELECT 中引用它們
WITH
cte1 AS (SELECT a, b FROM table1),
cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;在包含 WITH 子句的語句中，每個 CTE 名稱都可以被引用，以存取對應的 CTE 結果集。
CTE 名稱可以在其他 CTE 中被引用，這使得 CTE 可以基於其他 CTE 來定義。
CTE 可以引用自身來定義遞迴 CTE。遞迴 CTE 的常見應用包括序列生成和遍歷階層式或樹狀結構的資料。
通用表表達式是 DML 語句語法中的可選部分。它們是使用 WITH 子句來定義的。
with_clause:
WITH [RECURSIVE]
cte_name [(col_name [, col_name] ...)] AS (subquery)
[, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...
cte_name 命名一個單一通用表表達式，並且可以在包含 WITH 子句的語句中作為表參考來使用。
AS ( 的 subquery)subquery 部分稱為 「CTE 的子查詢」，它會產生 CTE 結果集。在 AS 後面的括號是必需的。
如果通用表表達式的子查詢引用了它自己的名稱，則該通用表表達式是遞迴的。如果 WITH 子句中的任何 CTE 是遞迴的，則必須包含 RECURSIVE 關鍵字。如需更多資訊，請參閱遞迴通用表表達式。
給定 CTE 的欄位名稱的決定方式如下
如果 CTE 名稱後面跟著一個括號中的名稱列表，則這些名稱就是欄位名稱。
WITH cte (col1, col2) AS ( SELECT 1, 2 UNION ALL SELECT 3, 4 ) SELECT col1, col2 FROM cte;列表中的名稱數量必須與結果集中的欄位數量相同。
否則，欄位名稱來自
AS (部分中第一個subquery)SELECT的選取列表。WITH cte AS ( SELECT 1 AS col1, 2 AS col2 UNION ALL SELECT 3, 4 ) SELECT col1, col2 FROM 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 子句。
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，但不能引用在較內層級的查詢區塊中定義的 CTE。
為了處理具有相同名稱的物件的引用，衍生表會隱藏 CTE；而 CTE 會隱藏基本表、TEMPORARY 表和視圖。名稱解析會先搜尋相同查詢區塊中的物件，然後在沒有找到具有該名稱的物件時，依序搜尋較外的區塊。
有關特定於遞迴 CTE 的其他語法考量，請參閱遞迴通用表表達式。
遞迴通用表表達式是指子查詢引用自身名稱的通用表表達式。例如：
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 |
+------+遞迴 CTE 具有此結構：
如果
WITH子句中的任何 CTE 引用自身，則WITH子句必須以WITH RECURSIVE開頭。（如果沒有 CTE 引用自身，則允許使用RECURSIVE，但不是必需的。）如果您忘記了遞迴 CTE 的
RECURSIVE，則很可能會產生此錯誤：ERROR 1146 (42S02): Table 'cte_name' doesn't exist遞迴 CTE 子查詢有兩個部分，以
UNION ALL或UNION [DISTINCT]分隔。SELECT ... -- return initial row set UNION ALL SELECT ... -- return additional row sets第一個
SELECT會產生 CTE 的初始列或多個列，並且不引用 CTE 名稱。第二個SELECT會產生其他列，並透過在其FROM子句中引用 CTE 名稱來進行遞迴。當這部分不再產生新列時，遞迴就會結束。因此，遞迴 CTE 由非遞迴的SELECT部分和遞迴的SELECT部分組成。CTE 結果欄位的類型僅從非遞迴
SELECT部分的欄位類型推斷，並且所有欄位都可為 null。對於類型判斷，將忽略遞迴SELECT部分。如果非遞迴和遞迴部分以
UNION DISTINCT分隔，則會刪除重複的列。這對於執行遞移閉包的查詢很有用，可以避免無限迴圈。遞迴部分的每個迭代僅對前一個迭代產生的列進行運算。如果遞迴部分有多個查詢區塊，則每個查詢區塊的迭代會以未指定的順序排程，並且每個查詢區塊都會對自上次迭代結束以來由其先前迭代或由其他查詢區塊產生的列進行運算。
前面顯示的遞迴 CTE 子查詢具有此非遞迴部分，該部分會擷取單列以產生初始列集：
SELECT 1CTE 子查詢也具有此遞迴部分：
SELECT n + 1 FROM cte WHERE n < 5在每次迭代時，該 SELECT 會產生一個新值的列，該值比先前列集中 n 的值大 1。第一次迭代會對初始列集 (1) 進行運算並產生 1+1=2；第二次迭代會對第一次迭代的列集 (2) 進行運算並產生 2+1=3；依此類推。此過程會持續到遞迴結束，遞迴會在 n 不再小於 5 時結束。
如果 CTE 的遞迴部分針對欄位產生比非遞迴部分更寬的值，則可能需要在非遞迴部分中加寬欄位，以避免資料截斷。請考慮以下語句：
WITH RECURSIVE cte AS
(
SELECT 1 AS n, 'abc' AS str
UNION ALL
SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
)
SELECT * FROM cte;在非嚴格 SQL 模式下，語句會產生此輸出：
+------+------+
| n | str |
+------+------+
| 1 | abc |
| 2 | abc |
| 3 | abc |
+------+------+str 欄位值皆為 'abc'，因為非遞迴 SELECT 會決定欄位寬度。因此，遞迴 SELECT 產生的較寬的 str 值會被截斷。
在嚴格 SQL 模式下，語句會產生錯誤：
ERROR 1406 (22001): Data too long for column 'str' at row 1為了解決此問題，使語句不會產生截斷或錯誤，請在非遞迴 SELECT 中使用 CAST()，以加寬 str 欄位：
WITH RECURSIVE cte AS
(
SELECT 1 AS n, CAST('abc' AS CHAR(20)) AS str
UNION ALL
SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
)
SELECT * FROM cte;現在，語句會產生此結果，而沒有截斷：
+------+--------------+
| n | str |
+------+--------------+
| 1 | abc |
| 2 | abcabc |
| 3 | abcabcabcabc |
+------+--------------+欄位是依名稱存取的，而不是依位置存取的，這表示遞迴部分中的欄位可以存取非遞迴部分中位置不同的欄位，如以下 CTE 所示：
WITH RECURSIVE cte AS
(
SELECT 1 AS n, 1 AS p, -1 AS q
UNION ALL
SELECT n + 1, q * 2, p * 2 FROM cte WHERE n < 5
)
SELECT * FROM cte;因為一列中的 p 是從前一列中的 q 衍生而來的，反之亦然，因此正值和負值會在輸出的每個連續列中交換位置。
+------+------+------+
| n | p | q |
+------+------+------+
| 1 | 1 | -1 |
| 2 | -2 | 2 |
| 3 | 4 | -4 |
| 4 | -8 | 8 |
| 5 | 16 | -16 |
+------+------+------+有些語法限制適用於遞迴 CTE 子查詢：
遞迴
SELECT部分不得包含以下結構：彙總函數，例如
SUM()視窗函數
GROUP BYORDER BYDISTINCT
遞迴的
SELECT部分遞迴 CTE 也可以使用LIMIT子句，以及可選的OFFSET子句。對結果集的影響與在最外層SELECT中使用LIMIT時相同，但效率更高，因為在遞迴SELECT中使用它會在產生請求的行數後立即停止產生行。DISTINCT的限制僅適用於UNION成員；允許使用UNION DISTINCT。遞迴的
SELECT部分必須僅引用 CTE 一次，且只能在其FROM子句中，而不能在任何子查詢中。它可以引用 CTE 以外的表，並將它們與 CTE 連接。如果像這樣在連接中使用，CTE 不得位於LEFT JOIN的右側。
這些限制來自 SQL 標準，除了先前提到 MySQL 特有的排除項。
對於遞迴 CTE，遞迴 SELECT 部分的 EXPLAIN 輸出列會在 Extra 欄位中顯示 Recursive。
EXPLAIN 顯示的成本估算代表每次迭代的成本，可能與總成本有很大差異。最佳化工具無法預測迭代次數，因為它無法預測 WHERE 子句何時會變成 false。
CTE 的實際成本也可能受到結果集大小的影響。產生許多行的 CTE 可能需要一個足夠大的內部臨時表，以便從記憶體中轉換為磁碟格式，並可能導致效能損失。如果是這樣，增加允許的記憶體中臨時表大小可能會提高效能；請參閱 第 10.4.4 節，「MySQL 中的內部臨時表使用」。
對於遞迴 CTE，遞迴 SELECT 部分包含終止遞迴的條件非常重要。作為一種開發技術來防範失控的遞迴 CTE，您可以透過限制執行時間來強制終止。
cte_max_recursion_depth系統變數強制限制 CTE 的遞迴層級數。伺服器會終止任何遞迴層級超過此變數值的 CTE 的執行。max_execution_time系統變數會對目前工作階段中執行的SELECT陳述式強制執行執行逾時。MAX_EXECUTION_TIME最佳化工具提示對它出現的SELECT陳述式強制執行每次查詢的執行逾時。
假設遞迴 CTE 在寫作時錯誤地沒有遞迴執行終止條件
WITH RECURSIVE cte (n) AS
(
SELECT 1
UNION ALL
SELECT n + 1 FROM cte
)
SELECT * FROM cte;預設情況下，cte_max_recursion_depth 的值為 1000，導致 CTE 在遞迴超過 1000 層時終止。應用程式可以變更工作階段值以調整其需求。
SET SESSION cte_max_recursion_depth = 10; -- permit only shallow recursion
SET SESSION cte_max_recursion_depth = 1000000; -- permit deeper recursion您也可以設定全域 cte_max_recursion_depth 值，以影響後續開始的所有工作階段。
對於執行速度較慢或在有理由將 cte_max_recursion_depth 值設定得非常高的情況下執行的查詢，另一種防範深度遞迴的方法是設定每個工作階段的逾時。若要執行此操作，請在執行 CTE 陳述式之前執行類似以下的陳述式
SET max_execution_time = 1000; -- impose one second timeout或者，在 CTE 陳述式本身中包含最佳化工具提示
WITH RECURSIVE cte (n) AS
(
SELECT 1
UNION ALL
SELECT n + 1 FROM cte
)
SELECT /*+ SET_VAR(cte_max_recursion_depth = 1M) */ * FROM cte;
WITH RECURSIVE cte (n) AS
(
SELECT 1
UNION ALL
SELECT n + 1 FROM cte
)
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM cte;您也可以在遞迴查詢中使用 LIMIT，以對傳回至最外層 SELECT 的最大行數施加限制，例如
WITH RECURSIVE cte (n) AS
(
SELECT 1
UNION ALL
SELECT n + 1 FROM cte LIMIT 10000
)
SELECT * FROM cte;您可以額外執行此操作或取代設定時間限制。因此，以下 CTE 會在傳回一萬行或執行一秒（1000 毫秒）後終止，以先發生者為準。
WITH RECURSIVE cte (n) AS
(
SELECT 1
UNION ALL
SELECT n + 1 FROM cte LIMIT 10000
)
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM cte;如果沒有執行時間限制的遞迴查詢進入無限迴圈，您可以使用 KILL QUERY 從另一個工作階段終止它。在工作階段本身內，用於執行查詢的用戶端程式可能會提供一種終止查詢的方法。例如，在 mysql 中，輸入 Control+C 會中斷目前的陳述式。
如先前所述，遞迴 Common Table Expression (CTE) 經常使用於序列產生和遍歷階層式或樹狀結構資料。本節將展示這些技術的一些簡單範例。
費波那契數列產生
費波那契數列以兩個數字 0 和 1（或 1 和 1）開始，之後的每個數字都是前兩個數字的和。如果遞迴 SELECT 產生的每一行都可以存取數列中的前兩個數字，則遞迴 Common Table Expression 可以產生費波那契數列。以下 CTE 使用 0 和 1 作為前兩個數字產生 10 個數字的數列
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;CTE 產生此結果
+------+-------+------------+
| 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 |
+------+-------+------------+CTE 的運作方式
n是一個顯示欄，用於指示該行包含第n個費波那契數。例如，第 8 個費波那契數是 13。fib_n欄顯示費波那契數n。next_fib_n欄顯示數字n之後的下一個費波那契數。此欄將下一個序列值提供給下一行，以便該行可以在其fib_n欄中產生前兩個序列值的總和。當
n達到 10 時，遞迴結束。這是一個隨意的選擇，目的是將輸出限制為一小組行。
前面的輸出顯示整個 CTE 結果。若要僅選取其中的一部分，請將適當的 WHERE 子句新增至最上層的 SELECT。例如，若要選取第 8 個費波那契數，請執行此操作
mysql> WITH RECURSIVE fibonacci ...
...
SELECT fib_n FROM fibonacci WHERE n = 8;
+-------+
| fib_n |
+-------+
| 13 |
+-------+日期序列產生
Common Table Expression 可以產生一系列連續的日期，這對於產生包含該系列中所有日期的行的摘要非常有用，包括摘要資料中未表示的日期。
假設銷售額表格包含以下這些行
mysql> SELECT * FROM sales ORDER BY date, price;
+------------+--------+
| date | price |
+------------+--------+
| 2017-01-03 | 100.00 |
| 2017-01-03 | 200.00 |
| 2017-01-06 | 50.00 |
| 2017-01-08 | 10.00 |
| 2017-01-08 | 20.00 |
| 2017-01-08 | 150.00 |
| 2017-01-10 | 5.00 |
+------------+--------+此查詢會摘要每天的銷售額
mysql> SELECT date, SUM(price) AS sum_price
FROM sales
GROUP BY date
ORDER BY date;
+------------+-----------+
| date | sum_price |
+------------+-----------+
| 2017-01-03 | 300.00 |
| 2017-01-06 | 50.00 |
| 2017-01-08 | 180.00 |
| 2017-01-10 | 5.00 |
+------------+-----------+但是，該結果包含表格跨越的日期範圍中未表示的日期的「漏洞」。可以使用遞迴 CTE 產生該組日期，並使用 LEFT JOIN 連接銷售資料，產生表示該範圍內所有日期的結果。
這是產生日期範圍序列的 CTE
WITH RECURSIVE dates (date) AS
(
SELECT MIN(date) FROM sales
UNION ALL
SELECT date + INTERVAL 1 DAY FROM dates
WHERE date + INTERVAL 1 DAY <= (SELECT MAX(date) FROM sales)
)
SELECT * FROM dates;CTE 產生此結果
+------------+
| date |
+------------+
| 2017-01-03 |
| 2017-01-04 |
| 2017-01-05 |
| 2017-01-06 |
| 2017-01-07 |
| 2017-01-08 |
| 2017-01-09 |
| 2017-01-10 |
+------------+CTE 的運作方式
將 CTE 與 sales 表格進行 LEFT JOIN 連接會產生銷售摘要，該摘要包含範圍內每個日期的行
WITH RECURSIVE dates (date) AS
(
SELECT MIN(date) FROM sales
UNION ALL
SELECT date + INTERVAL 1 DAY FROM dates
WHERE date + INTERVAL 1 DAY <= (SELECT MAX(date) FROM sales)
)
SELECT dates.date, COALESCE(SUM(price), 0) AS sum_price
FROM dates LEFT JOIN sales ON dates.date = sales.date
GROUP BY dates.date
ORDER BY dates.date;輸出如下所示
+------------+-----------+
| date | sum_price |
+------------+-----------+
| 2017-01-03 | 300.00 |
| 2017-01-04 | 0.00 |
| 2017-01-05 | 0.00 |
| 2017-01-06 | 50.00 |
| 2017-01-07 | 0.00 |
| 2017-01-08 | 180.00 |
| 2017-01-09 | 0.00 |
| 2017-01-10 | 5.00 |
+------------+-----------+一些值得注意的事項
查詢效率不高嗎？特別是遞迴
SELECT中的每一行都會執行MAX()子查詢的查詢？EXPLAIN顯示，包含MAX()的子查詢僅評估一次，且結果會被快取。使用
COALESCE()可避免在sales表格中沒有銷售資料的日期，於sum_price欄中顯示NULL。
階層式資料遍歷
遞迴 Common Table Expression 對於遍歷形成階層的資料非常有用。請考慮以下陳述式，這些陳述式會建立一個小資料集，其中顯示公司中每位員工的員工姓名和 ID 號碼，以及員工經理的 ID。最上層的員工（執行長）的經理 ID 為 NULL（沒有經理）。
CREATE TABLE employees (
id INT PRIMARY KEY NOT NULL,
name VARCHAR(100) NOT NULL,
manager_id INT NULL,
INDEX (manager_id),
FOREIGN KEY (manager_id) REFERENCES employees (id)
);
INSERT INTO employees VALUES
(333, "Yasmina", NULL), # Yasmina is the CEO (manager_id is NULL)
(198, "John", 333), # John has ID 198 and reports to 333 (Yasmina)
(692, "Tarek", 333),
(29, "Pedro", 198),
(4610, "Sarah", 29),
(72, "Pierre", 29),
(123, "Adil", 692);產生的資料集如下所示
mysql> SELECT * FROM employees ORDER BY id;
+------+---------+------------+
| id | name | manager_id |
+------+---------+------------+
| 29 | Pedro | 198 |
| 72 | Pierre | 29 |
| 123 | Adil | 692 |
| 198 | John | 333 |
| 333 | Yasmina | NULL |
| 692 | Tarek | 333 |
| 4610 | Sarah | 29 |
+------+---------+------------+若要產生每個員工的管理鏈的組織結構圖（即從執行長到員工的路徑），請使用遞迴 CTE
WITH RECURSIVE employee_paths (id, name, path) AS
(
SELECT id, name, CAST(id AS CHAR(200))
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, CONCAT(ep.path, ',', e.id)
FROM employee_paths AS ep JOIN employees AS e
ON ep.id = e.manager_id
)
SELECT * FROM employee_paths ORDER BY path;CTE 產生此輸出
+------+---------+-----------------+
| id | name | path |
+------+---------+-----------------+
| 333 | Yasmina | 333 |
| 198 | John | 333,198 |
| 29 | Pedro | 333,198,29 |
| 4610 | Sarah | 333,198,29,4610 |
| 72 | Pierre | 333,198,29,72 |
| 692 | Tarek | 333,692 |
| 123 | Adil | 333,692,123 |
+------+---------+-----------------+CTE 的運作方式
若要尋找特定員工或多位員工的路徑，請將 WHERE 子句新增至最上層的 SELECT。例如，若要顯示 Tarek 和 Sarah 的結果，請像這樣修改 SELECT
mysql> WITH RECURSIVE ...
...
SELECT * FROM employees_extended
WHERE id IN (692, 4610)
ORDER BY path;
+------+-------+-----------------+
| id | name | path |
+------+-------+-----------------+
| 4610 | Sarah | 333,198,29,4610 |
| 692 | Tarek | 333,692 |
+------+-------+-----------------+Common Table Expression (CTE) 在某些方面與衍生表格相似
這兩種結構都有名稱。
這兩種結構都存在於單一陳述式的範圍內。
由於這些相似之處，CTE 和衍生表格通常可以互換使用。作為一個簡單的範例，以下陳述式是等效的
WITH cte AS (SELECT 1) SELECT * FROM cte;
SELECT * FROM (SELECT 1) AS dt;但是，CTE 相較於衍生表格有一些優勢
衍生表格在查詢中只能被引用一次。CTE 可以被引用多次。若要使用衍生表格結果的多個執行個體，您必須多次衍生結果。
CTE 可以自我引用（遞迴）。
一個 CTE 可以引用另一個 CTE。
當 CTE 的定義出現在語句的開頭，而不是嵌入在語句內時，可能會更容易閱讀。
CTE 類似於使用 CREATE [TEMPORARY] TABLE 建立的表格，但不需要明確定義或刪除。對於 CTE，您不需要任何建立表格的權限。