前言:

這里一般來說需要編一個故事但是我懶

mysql遞歸CTE: 8.0版本以上才有WITH AS,8.0以下版本的話請繞行----->不是說8.0以下不能寫遞歸只是不是這個文章的寫法,所以看了也沒用不用浪費(fèi)時間

文檔原話:

文檔英文原話:

官方文檔鏈接

先上可以cv的,不著急寫需求的可以往下看看或者看不懂的話可以往下看看

sql語句

# n: 迭代次數(shù)
# id, name, parentId: 想要查詢的字段,根據(jù)自己需求進(jìn)行修改
# cte_test_paths: 儲存區(qū)名字是自己創(chuàng)建的這個地方需要和最后的SELECT * FROM cte_test_paths WHERE n = 1;中的表名相同
WITH RECURSIVE cte_test_paths (n, id, name, parentId) AS
                   (
                       # 0 AS n的0表示第一次遞歸從零開始計數(shù),因?yàn)橐话銇碚f第一次遞歸會查詢最高級,一般情況下的最高級,而不是真正的第一級,可以根據(jù)業(yè)務(wù)變更
                       SELECT 0 AS n,
                              id,
                              name,
                              parentId
                              # cte_test: 你需要取遞歸的表,這里需要注意的是遞歸公用表表達(dá)式'cte_test_paths'在遞歸查詢塊中既不能包含聚合函數(shù)也不能包含窗口函數(shù)
                       from cte_test
                       # WHERE后接需要查詢的條件,比如這里是第一代的id,也就是第一代的標(biāo)志,
                       WHERE id = 1
                       UNION ALL
                       SELECT n + 1, e.id, e.name, e.parentId
                       FROM cte_test_paths AS etp
                                # 這里聯(lián)表的條件是遞歸的上級id和下級id的關(guān)系,需要根據(jù)自己的實(shí)際環(huán)境進(jìn)行修改
                                JOIN cte_test AS e ON etp.id = e.parentId
                       # 這里的 n<1 是為了限制迭代次數(shù)避免無限迭代浪費(fèi)性能,比如說我只需要查詢兩代了的話,但是不做代數(shù)限制,卻查了所有代,這是沒有必要的浪費(fèi)
                       WHERE n < 1)
SELECT *
FROM cte_test_paths
# 這里之所以做條件查詢的原因是因?yàn)?我只想看到第一代(因?yàn)槲业牡谝淮遣话?這里的根代的意思就是一個第一代都要屬于他子節(jié)點(diǎn)的最頂級,所以根據(jù)上述 0 AS n 第一代并不是0而是1)
WHERE n = 1;

文檔翻譯的

遞歸公用表表達(dá)式

CTE 可以指代自身或其他 CTE：

• 自引用 CTE 是遞歸的。

• CTE 可以指在同一WITH子句中較早定義的 CTE ，但不能指稍后定義的CTE 。

  此約束排除了相互遞歸的 CTE，其中 cte1引用cte2 和cte2引用 cte1。其中一個引用必須是稍后定義的 CTE，這是不允許的。

• 給定查詢塊中的 CTE 可以指在更外層的查詢塊中定義的 CTE，但不能指在更內(nèi)層的查詢塊中定義的 CTE。

為了解析對同名對象的引用，派生表隱藏了 CTE；和 CTE 隱藏基表、 TEMPORARY表和視圖。名稱解析通過在同一查詢塊中搜索對象，然后在沒有找到具有該名稱的對象的情況下依次進(jìn)行外部塊來進(jìn)行。

與派生表一樣，CTE 不能包含 MySQL 8.0.14 之前的外部引用。這是 MySQL 8.0.14 中解除的 MySQL 限制，而不是 SQL 標(biāo)準(zhǔn)的限制。有關(guān)特定于遞歸 CTE 的其他語法注意事項(xiàng)，請參閱 遞歸公用表表達(dá)式

遞歸公用表表達(dá)式是具有引用其自身名稱的子查詢的表達(dá)式。例如：

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

執(zhí)行時，該語句會產(chǎn)生以下結(jié)果，即包含簡單線性序列的單列：

遞歸 CTE 具有以下結(jié)構(gòu)：

• 如果WITH子句中的任何CTE引用自身，則with子句必須以 WITH RECURSIVE開頭。(如果沒有CTE引用自身，則允許遞歸，但不是必需的。)

  如果您忘記了遞歸CTE的RECURSIVE，則可能會導(dǎo)致此錯誤:

  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名稱來生成其他行和遞歸。當(dāng)此部分不生成新行時，遞歸結(jié)束。因此，遞歸CTE由非遞歸SELECT部分和遞歸SELECT部分組成。

  每個SELECT部分本身可以是多個SELECT 語句的聯(lián)合。

• CTE結(jié)果列的類型SELECT僅從非遞歸部分的列類型推斷出來 ，列都是可以為空的。對于類型確定，遞歸SELECT部分將被忽略。

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

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

前面顯示的遞歸 CTE 子查詢具有此非遞歸部分，它檢索單個行以生成初始行集：

SELECT 1

CTE 子查詢也有這個遞歸部分：

SELECT n + 1 FROM cte WHERE n < 5

在每次迭代中，該SELECT生成一行，其新值大于上一行集中的值n。第一次迭代對初始行集 (1) 進(jìn)行操作，并產(chǎn)生1 + 1 = 2; 第二次迭代對第一次迭代的行集 (2) 進(jìn)行操作并產(chǎn)生2 + 1 = 3; 等等。這一直持續(xù)到遞歸結(jié)束，當(dāng)n不小于5時發(fā)生。

如果CTE的遞歸部分比非遞歸部分產(chǎn)生更寬的列值，則可能需要加寬非遞歸部分中的列以避免數(shù)據(jù)截斷?？紤]以下陳述:

WITH RECURSIVE cte AS
(
  SELECT 1 AS n, 'abc' AS str
  UNION ALL
  SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3 
  # 別查了CONCAT拼接字符串函數(shù)
)
SELECT * FROM cte;

在非嚴(yán)格 SQL 模式下，該語句產(chǎn)生以下輸出：

+------+------+
| n    | str  |
+------+------+
|    1 | abc  |
|    2 | abc  |
|    3 | abc  |
+------+------+

的str列值都是 'abc'因?yàn)榉沁f歸 SELECT確定列寬。因此，str遞歸產(chǎn)生的更廣泛的值SELECT 被截斷。

在嚴(yán)格的 SQL 模式下，該語句會產(chǎn)生錯誤：

ERROR 1406 (22001): Data too long for column 'str' at row 1

要解決此問題，使語句不會產(chǎn)生截斷或錯誤，請CAST() 在非遞歸中使用SELECT以使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;

現(xiàn)在語句產(chǎn)生這個結(jié)果，沒有截斷：

+------+--------------+
| 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;

因?yàn)?code>p一行是q從前一行派生的 ，反之亦然，正負(fù)值在輸出的每一行中交換位置：(這里不明白的話自己算一下就知道了)

+------+------+------+
| n    | p    | q    |
+------+------+------+
|    1 |    1 |   -1 |
|    2 |   -2 |    2 |
|    3 |    4 |   -4 |
|    4 |   -8 |    8 |
|    5 |   16 |  -16 |
+------+------+------+

一些語法約束適用于遞歸 CTE 子查詢：

• 遞歸SELECT部分不得包含以下結(jié)構(gòu)：

  • 聚合函數(shù)，例如 SUM()
  • Window functions
  • GROUP BY
  • ORDER BY
  • DISTINCT

  在MySQL 8.0.19之前，遞歸CTE的遞歸選擇部分也不能使用LIMIT子句。在MySQL 8.0.19中取消了此限制，現(xiàn)在在這種情況下支持LIMIT以及可選的OFFSET子句。對結(jié)果集的影響與在最外層選擇中使用限制時相同，但也更有效，由于將其與遞歸選擇一起使用，因此一旦生成了請求的行數(shù)，就會停止生成行數(shù)。

  這些約束不適用于遞歸CTE的非遞歸選擇部分。對DISTINCT的禁止僅適用于工會成員; 允許使用UNION DISTINCT。

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

這些約束來自 SQL 標(biāo)準(zhǔn)，除了 MySQL 特定的ORDER BY、 LIMIT（MySQL 8.0.18 及更早版本）和 DISTINCT排除項(xiàng).

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

EXPLAIN顯示的成本估算表示每次迭代的成本，可能與總成本有很大不同。優(yōu)化器無法預(yù)測迭代次數(shù)，因?yàn)樗鼰o法預(yù)測WHERE子句在什么時候變?yōu)閒alse。

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

限制公用表表達(dá)式遞歸

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

• 該cte_max_recursion_depth 系統(tǒng)變量對CTE的遞歸級別數(shù)量進(jìn)行限制。服務(wù)器終止任何遞歸級別超過此變量值的 CTE 的執(zhí)行。

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

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

  ps:下面兩個時間我也是在沒看明白,要是有大佬看見的話麻煩解釋一二

假設(shè)在沒有遞歸執(zhí)行終止條件的情況下錯誤地編寫了遞歸 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 在遞歸超過 1000 個級別時終止。應(yīng)用程序可以更改會話值以根據(jù)其要求進(jìn)行調(diào)整：

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 /*+ 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;

從 MySQL 8.0.19 開始，您還可以 LIMIT在遞歸查詢中使用來強(qiáng)加要返回到最外層的最大行數(shù) SELECT，例如：

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

除了或代替設(shè)置時間限制，您還可以執(zhí)行此操作。因此，以下 CTE 在返回一萬行或運(yùn)行一秒（1000 毫秒）后終止，以先發(fā)生者為準(zhǔn)：

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

如果沒有執(zhí)行時間限制的遞歸查詢進(jìn)入無限循環(huán)，您可以使用 KILL QUERY. 在會話本身內(nèi)，用于運(yùn)行查詢的客戶端程序可能會提供終止查詢的方法。例如，在 mysql 中，輸入Control+C 會 中斷當(dāng)前語句。

創(chuàng)建測試單表數(shù)據(jù)

• 測試表cte_test創(chuàng)建

  create table cte_test
  (
      id       bigint  not null comment 'ID',
      parentId bigint  not null comment '爹id',
      name     char(3) null comment '名字',
      unique (id)
  )
      comment '遞歸測試表';
  

• 插入測試數(shù)據(jù)

  INSERT INTO cte_test (id, parentId, name) VALUES (1, 0, '用戶1');
  INSERT INTO cte_test (id, parentId, name) VALUES (2, 1, '用戶2');
  INSERT INTO cte_test (id, parentId, name) VALUES (3, 1, '用戶3');
  INSERT INTO cte_test (id, parentId, name) VALUES (4, 1, '用戶4');
  INSERT INTO cte_test (id, parentId, name) VALUES (5, 4, '用戶5');
  INSERT INTO cte_test (id, parentId, name) VALUES (6, 4, '用戶6');
  INSERT INTO cte_test (id, parentId, name) VALUES (7, 6, '用戶7');
  INSERT INTO cte_test (id, parentId, name) VALUES (8, 7, '用戶8');
  

• 查詢第一代的所有用戶

  • sql

    WITH RECURSIVE cte_test_paths (n, id, name, parentId) AS
                       (
                           SELECT 0 AS n, id, name, parentId
                           from cte_test
                           WHERE id = 1
                           UNION ALL
                           SELECT n + 1, e.id, e.name, e.parentId
                           FROM cte_test_paths AS etp
                                    JOIN cte_test AS e ON etp.id = e.parentId
                           WHERE n < 1)
    SELECT *
    FROM cte_test_paths
    WHERE n = 1;
    

  • 數(shù)據(jù)

    

• 查詢所有用戶和代數(shù)

  • sql

    WITH RECURSIVE cte_test_paths (n, id, name, parentId) AS
                       (
                           SELECT 0 AS n, id, name, parentId
                           from cte_test
                           WHERE id = 1
                           UNION ALL
                           SELECT n + 1, e.id, e.name, e.parentId
                           FROM cte_test_paths AS etp
                                    JOIN cte_test AS e ON etp.id = e.parentId
                       )
    SELECT *
    FROM cte_test_paths;
    

  • 數(shù)據(jù)

    

總結(jié) 

到此這篇關(guān)于MySQL遞歸sql語句WITH表達(dá)式實(shí)現(xiàn)的文章就介紹到這了,更多相關(guān)MySQL遞歸sql語句WITH表達(dá)式內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論