mysql遞迴函數with recursive的用法舉例

2022-08-16 18:04:44

AS 用法:

AS在mysql用來給列/表起別名.

有時，列的名稱是一些表示式，使查詢的輸出很難理解。要給列一個描述性名稱，可以使用列別名。

要給列新增別名，可以使用AS關鍵詞後跟別名

例子1:

SELECT 
 [column_1 | expression] AS col_name
FROM table_name;

如果別名包含空格，則必須參照以下內容：

例子2:

SELECT 
 [column_1 | expression] AS 'col name'
FROM table_name;

with(Common Table Expressions/CTE)用法:

with在mysql中被稱為公共表示式,可以作為一個臨時表然後在其他結構中呼叫.如果是自身呼叫那麼就是後面講的遞迴.

語法:

with_clause:
    WITH [RECURSIVE]
        cte_name [(col_name [, col_name] ...)] AS (subquery)
        [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...

cte_name :公共表示式的名稱,可以理解為表名,用來表示as後面跟著的子查詢

col_name :公共表示式包含的列名,可以寫也可以不寫

例子1:

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;

例子2:

WITH cte (col1, col2) AS
(
  SELECT 1, 2
  UNION ALL
  SELECT 3, 4
)
SELECT col1, col2 FROM cte;

例子3:

這裡的第一個as後面接的是子查詢,第二個as表示列名,而不是子查詢.

WITH cte AS
(
  SELECT 1 AS col1, 2 AS col2
  UNION ALL
  SELECT 3, 4
)
SELECT col1, col2 FROM cte;

with的合法用法:

在子查詢(包括派生的表子查詢)的開始處

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

同一級別只允許一個WITH子句。同一級別的WITH後面跟著WITH是不允許的,下面是非法用法:

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

改為合法用法:

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

在這裡面as代表列名,sql不是順序執行的,這一點了解的話就很好理解這個as了

簡單遞迴用法:

首先我們引出一個問題: 什麼叫做遞迴?

遞迴:給定函數初始條件,然後反覆呼叫自身直到終止條件.

例子1:遞迴得到依次遞增的序列:

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

官方檔案中對於這個寫法的解釋:

At each iteration, that SELECT produces a row with a new value one greater than the value of n from the previous row set. The first iteration operates on the initial row set (1) and produces 1+1=2; the second iteration operates on the first iteration’s row set (2) and produces 2+1=3; and so forth. This continues until recursion ends, which occurs when n is no longer less than 5.

用python實現就是:

def cte(n):
	print(n)
	if n<5:
		cte(n+1)

也就是說,一個with recursive 由兩部分組成.第一部分是非遞迴部分(union all上方),第二部分是遞迴部分(union all下方).遞迴部分第一次進入的時候使用非遞迴部分傳遞過來的引數,也就是第一行的資料值,進而得到第二行資料值.然後根據第二行資料值得到第三行資料值.

例子2:遞迴得到不斷複製的字串

這裡的as表示列名,表示說這個CTE有兩個列,也可以寫為with cte(n,str) as (subquery)

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;

結果:

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

這裡的話concat是每一次都連線一個str,這個str來自上一行的結果,但是最終輸出卻是每一行都沒有變化的值,這是為什麼?
這是因為我們在宣告str的時候限制了它的字元長度,使用 型別轉換CAST(‘abc’ AS CHAR(30)) 就可以得到複製的字串了.
**注意:**這裡也可能會報錯,看mysql模式.在嚴格模式下這裡會顯示Error Code: 1406. Data too long for column 'str' at row 1

關於strict SQL mode和nonstrict SQL mode:mysql 嚴格模式 Strict Mode說明

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

當然,如果上一行的值有多個,我們還可以對多個值進行重新組合得到我們想要的結果,比如下面這個例子.

例子3:生成斐波那契數列

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;

結果:

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

語法說明:

UNION ALL與UNION DISTINCT UNION ALL:

UNION ALL:
非遞迴部分和遞迴部分用UNION ALL分隔,那麼所有的行都會被加入到最後的表中
UNION DISTINCT:
非遞迴部分和遞迴部分用UNION DISTINCT分隔，重複的行被消除。這對於執行傳遞閉包的查詢非常有用，以避免無限迴圈。

limit控制遞迴次數

recursive(第二個select)不能使用的結構:

官網的描述:

The recursive SELECT part must not contain these constructs:

Aggregate functions such as SUM()

Window functions

GROUP BY

ORDER BY

DISTINCT

限制遞迴次數/時間:

當出現不符合設定情況的會報錯,分為以下幾種設定方法:

cte_max_recursion_depth :default 設定為1000,表達遞迴的層數.可以使用如下語句修改這個值:

SET SESSION cte_max_recursion_depth = 10;      -- permit only shallow recursion
SET SESSION cte_max_recursion_depth = 1000000; -- permit deeper recursion

當然也可以設定為global,也就是set global cte_max_recursion_depth = 1000000;這樣子就對全域性的遞迴都有限制

max_execution_time :設定最近的遞迴時間

SET max_execution_time = 1000; -- impose one second timeout

MAX_EXECUTION_TIME:設定全域性的遞迴時間

官網檔案說明如下:

The cte_max_recursion_depth system variable enforces a limit on the
number of recursion levels for CTEs. The server terminates execution
of any CTE that recurses more levels than the value of this variable.
The max_execution_time system variable enforces an execution timeout
for SELECT statements executed within the current session.
The MAX_EXECUTION_TIME optimizer hint enforces a per-query execution
timeout for the SELECT statement in which it appears.
limit:限之最大行的數量

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

補充：MySql8使用WITH RECURSIVE進行遞迴查詢下級節點資料

#查詢id=62的所有子節點
WITH RECURSIVE temp AS (
		SELECT * FROM tbsys_office o WHERE o.id=62
		UNION ALL
		SELECT o.* FROM tbsys_office o,temp t WHERE t.id=o.parent_id
) SELECT * FROM temp;

#查詢id=80的所有父節點
WITH RECURSIVE temp AS (
		SELECT * FROM tbsys_office o WHERE o.id=80
		UNION ALL
		SELECT o.* FROM tbsys_office o,temp t WHERE t.parent_id=o.id
) SELECT * FROM temp;