MySQL 9.0 版本說明
通用資料表運算式 (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子句
WITH ... SELECT ... WITH ... UPDATE ... WITH ... DELETE ...在子查詢的開頭（包括衍生資料表子查詢）
SELECT ... WHERE id IN (WITH ... SELECT ...) ... SELECT * FROM (WITH ... SELECT ...) AS dt ...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=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
遞迴 CTE 的遞迴
SELECT部分也可以使用LIMIT子句，以及可選的OFFSET子句。對結果集的影響與在最外層SELECT中使用LIMIT時相同，但效率更高，因為與遞迴SELECT一起使用時，一旦產生所要求的列數，就會停止產生列。對
DISTINCT的限制僅適用於UNION成員；允許使用UNION DISTINCT。遞迴
SELECT部分必須僅在它的FROM子句中，而不是在任何子查詢中引用 CTE 一次。它可以引用 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 會中斷目前的語句。
如前所述，遞迴通用表格表達式 (CTE) 經常被用於序列產生以及遍歷階層式或樹狀結構資料。本節將展示這些技術的一些簡單範例。
費波那契數列產生
費波那契數列以兩個數字 0 和 1（或 1 和 1）開始，之後的每個數字都是前兩個數字的總和。如果遞迴 SELECT 產生的每一列都可以存取數列中的前兩個數字，則遞迴通用表格表達式可以產生費波那契數列。以下 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 結果。若要僅選取其中的一部分，請在最上層 SELECT 中新增適當的 WHERE 子句。例如，若要選取第 8 個費波那契數，請執行此操作：
mysql> WITH RECURSIVE fibonacci ...
...
SELECT fib_n FROM fibonacci WHERE n = 8;
+-------+
| fib_n |
+-------+
| 13 |
+-------+日期數列產生
通用表格表達式可以產生一系列連續日期，這對於產生包含數列中所有日期的列的摘要很有用，包括摘要資料中未表示的日期。
假設銷售數字的表格包含以下列：
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()的子查詢僅計算一次，並且結果會被快取。在
sales表格中沒有銷售資料的日子裡，使用COALESCE()可避免在sum_price列中顯示NULL。
階層式資料遍歷
遞迴通用表格表達式對於遍歷形成階層的資料非常有用。請考慮以下語句，這些語句會建立一個小型資料集，該資料集會針對公司中的每位員工顯示員工姓名和 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 |
+------+-------+-----------------+通用資料表運算式 (CTE) 在某些方面與衍生資料表相似
這兩種結構都有名稱。
這兩種結構都僅存在於單一語法的範圍內。
由於這些相似之處，CTE 和衍生資料表通常可以互換使用。作為一個簡單的例子，這些語法是等效的
WITH cte AS (SELECT 1) SELECT * FROM cte;
SELECT * FROM (SELECT 1) AS dt;然而，CTE 比衍生資料表有一些優勢
衍生資料表在一個查詢中只能被引用一次。CTE 可以被多次引用。若要使用衍生資料表結果的多個實例，您必須多次衍生結果。
CTE 可以自我引用（遞迴）。
一個 CTE 可以參考另一個 CTE。
當 CTE 的定義出現在語法的開頭，而不是嵌入在語法中時，CTE 可能更容易閱讀。
CTE 類似於使用 CREATE [TEMPORARY] TABLE 建立的資料表，但不需要明確定義或刪除。對於 CTE，您不需要任何建立資料表的權限。