前言:

這里一般來(lái)說(shuō)需要編一個(gè)故事但是我懶

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

文檔原話:

文檔英文原話:

官方文檔鏈接

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

sql語(yǔ)句

# n: 迭代次數(shù)
# id, name, parentId: 想要查詢的字段,根據(jù)自己需求進(jìn)行修改
# cte_test_paths: 儲(chǔ)存區(qū)名字是自己創(chuàng)建的這個(gè)地方需要和最后的SELECT * FROM cte_test_paths WHERE n = 1;中的表名相同
WITH RECURSIVE cte_test_paths (n, id, name, parentId) AS
                   (
                       # 0 AS n的0表示第一次遞歸從零開(kāi)始計(jì)數(shù),因?yàn)橐话銇?lái)說(shuō)第一次遞歸會(huì)查詢最高級(jí),一般情況下的最高級(jí),而不是真正的第一級(jí),可以根據(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)表的條件是遞歸的上級(jí)id和下級(jí)id的關(guān)系,需要根據(jù)自己的實(shí)際環(huán)境進(jìn)行修改
                                JOIN cte_test AS e ON etp.id = e.parentId
                       # 這里的 n<1 是為了限制迭代次數(shù)避免無(wú)限迭代浪費(fèi)性能,比如說(shuō)我只需要查詢兩代了的話,但是不做代數(shù)限制,卻查了所有代,這是沒(méi)有必要的浪費(fèi)
                       WHERE n < 1)
SELECT *
FROM cte_test_paths
# 這里之所以做條件查詢的原因是因?yàn)?我只想看到第一代(因?yàn)槲业牡谝淮遣话?這里的根代的意思就是一個(gè)第一代都要屬于他子節(jié)點(diǎn)的最頂級(jí),所以根據(jù)上述 0 AS n 第一代并不是0而是1)
WHERE n = 1;

文檔翻譯的

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

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

• 自引用 CTE 是遞歸的。

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

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

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

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

與派生表一樣，CTE 不能包含 MySQL 8.0.14 之前的外部引用。這是 MySQL 8.0.14 中解除的 MySQL 限制，而不是 SQL 標(biāo)準(zhǔn)的限制。有關(guān)特定于遞歸 CTE 的其他語(yǔ)法注意事項(xiàng)，請(qǐ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í)行時(shí)，該語(yǔ)句會(huì)產(chǎn)生以下結(jié)果，即包含簡(jiǎn)單線性序列的單列：

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

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

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

  ERROR 1146 (42S02): Table 'cte_name' doesn't exist
  

• 遞歸 CTE 子查詢有兩個(gè)部分，由UNION [ALL\] 或分隔 UNION DISTINCT：

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

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

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

• CTE結(jié)果列的類型SELECT僅從非遞歸部分的列類型推斷出來(lái) ，列都是可以為空的。對(duì)于類型確定，遞歸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)行操作。

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

SELECT 1

CTE 子查詢也有這個(gè)遞歸部分：

SELECT n + 1 FROM cte WHERE n < 5

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

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

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 模式下，該語(yǔ)句產(chǎn)生以下輸出：

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

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

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

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

要解決此問(wèn)題，使語(yǔ)句不會(huì)產(chǎn)生截?cái)嗷蝈e(cuò)誤，請(qǐng)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)在語(yǔ)句產(chǎn)生這個(gè)結(jié)果，沒(méi)有截?cái)啵?/p>

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

列是按名稱而不是位置訪問(wèn)的(具體看一下下方例子就明白了)，這意味著遞歸部分中的列可以訪問(wèn)非遞歸部分中具有不同位置的列，如本 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 |
+------+------+------+

一些語(yǔ)法約束適用于遞歸 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子句。對(duì)結(jié)果集的影響與在最外層選擇中使用限制時(shí)相同，但也更有效，由于將其與遞歸選擇一起使用，因此一旦生成了請(qǐng)求的行數(shù)，就會(huì)停止生成行數(shù)。

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

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

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

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

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

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

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

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

• 該cte_max_recursion_depth 系統(tǒng)變量對(duì)CTE的遞歸級(jí)別數(shù)量進(jìn)行限制。服務(wù)器終止任何遞歸級(jí)別超過(guò)此變量值的 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ǔ)句。

  ps:下面兩個(gè)時(shí)間我也是在沒(méi)看明白,要是有大佬看見(jiàn)的話麻煩解釋一二

假設(shè)在沒(méi)有遞歸執(zhí)行終止條件的情況下錯(cuò)誤地編寫了遞歸 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 個(gè)級(jí)別時(shí)終止。應(yīng)用程序可以更改會(huì)話值以根據(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值以影響隨后開(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 /*+ 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 開(kāi)始，您還可以 LIMIT在遞歸查詢中使用來(lái)強(qiáng)加要返回到最外層的最大行數(shù) SELECT，例如：

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

除了或代替設(shè)置時(shí)間限制，您還可以執(zhí)行此操作。因此，以下 CTE 在返回一萬(wàn)行或運(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;

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

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

• 測(cè)試表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 '遞歸測(cè)試表';
  

• 插入測(cè)試數(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語(yǔ)句WITH表達(dá)式實(shí)現(xiàn)的文章就介紹到這了,更多相關(guān)MySQL遞歸sql語(yǔ)句WITH表達(dá)式內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論