在本教學中，您將瞭解MySQL遞歸CTE(公共表運算式)以及如何使用它來遍曆分層數據。
自MySQL 8.0版以來簡要介紹了公共表運算式或叫CTE的功能，因此需要您在電腦上安裝MySQL 8.0，以便在本教程中練習本語句。
1. MySQL遞歸CTE簡介
遞歸公用表運算式(CTE)是一個具有引用CTE名稱本身的子查詢的CTE。以下說明遞歸CTE的語法 -
WITH RECURSIVE cte_name AS (
initial_query -- anchor member
UNION ALL
recursive_query -- recursive member that references to the CTE name
)
SELECT * FROM cte_name;
遞歸CTE由三個主要部分組成：
- 形成CTE結構的基本結果集的初始查詢(initial_query)，初始查詢部分被稱為錨成員。
- 遞歸查詢部分是引用CTE名稱的查詢，因此稱為遞歸成員。遞歸成員由一個UNION ALL或
UNION DISTINCT運算符與錨成員相連。 - 終止條件是當遞歸成員沒有返回任何行時，確保遞歸停止。
遞歸CTE的執行順序如下：
- 首先，將成員分為兩個：錨點和遞歸成員。
- 接下來，執行錨成員形成基本結果集(
R0)，並使用該基本結果集進行下一次迭代。 - 然後，將
Ri結果集作為輸入執行遞歸成員，並將Ri+1作為輸出。 - 之後，重複第三步，直到遞歸成員返回一個空結果集，換句話說，滿足終止條件。
- 最後，使用
UNION ALL運算符將結果集從R0到Rn組合。
2. 遞歸成員限制
遞歸成員不能包含以下結構：
請注意，上述約束不適用於錨定成員。 另外，只有在使用UNION運算符時，要禁止DISTINCT才適用。 如果使用UNION DISTINCT運算符，則允許使用DISTINCT。
另外，遞歸成員只能在其子句中引用CTE名稱，而不是引用任何子查詢。
3. 簡單的MySQL遞歸CTE示例
請參閱以下簡單的遞歸CTE 示例：
WITH RECURSIVE cte_count (n)
AS (
SELECT 1
UNION ALL
SELECT n + 1
FROM cte_count
WHERE n < 3
)
SELECT n
FROM cte_count;
在此示例中，以下查詢：
SELECT 1
是作為基本結果集返回1的錨成員。
以下查詢 -
SELECT n + 1
FROM cte_count
WHERE n < 3
是遞歸成員，因為它引用了cte_count的CTE名稱。
遞歸成員中的運算式<3是終止條件。當n等於3，遞歸成員將返回一個空集合，將停止遞歸。
下圖顯示了上述CTE的元素：
遞歸CTE返回以下輸出：
遞歸CTE的執行步驟如下：
- 首先，分離錨和遞歸成員。
- 接下來，錨定成員形成初始行(
SELECT 1)，因此第一次迭代在n = 1時產生1 + 1 = 2。 - 然後，第二次迭代對第一次迭代的輸出(
2)進行操作，並且在n = 2時產生2 + 1 = 3。
- 之後，在第三次操作(
n = 3)之前，滿足終止條件(n <3)，因此查詢停止。 - 最後，使用
UNION ALL運算符組合所有結果集1,2和3。
4. 使用MySQL遞歸CTE遍曆分層數據
我們將使用示例資料庫(zaixiandb)中的employees表進行演示。
mysql> desc employees;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| employeeNumber | int(11) | NO | PRI | NULL | |
| lastName | varchar(50) | NO | | NULL | |
| firstName | varchar(50) | NO | | NULL | |
| extension | varchar(10) | NO | | NULL | |
| email | varchar(100) | NO | | NULL | |
| officeCode | varchar(10) | NO | MUL | NULL | |
| reportsTo | int(11) | YES | MUL | NULL | |
| jobTitle | varchar(50) | NO | | NULL | |
+----------------+--------------+------+-----+---------+-------+
8 rows in set
employees表具有引用employeeNumber字段的reportsTo字段。 reportsTo列存儲經理的ID。總經理不會向公司的組織結構中的任何人報告，因此reportsTo列中的值為NULL。
您可以應用遞歸CTE以自頂向下的方式查詢整個組織結構，如下所示：
WITH RECURSIVE employee_paths AS
( SELECT employeeNumber,
reportsTo managerNumber,
officeCode,
1 lvl
FROM employees
WHERE reportsTo IS NULL
UNION ALL
SELECT e.employeeNumber,
e.reportsTo,
e.officeCode,
lvl+1
FROM employees e
INNER JOIN employee_paths ep ON ep.employeeNumber = e.reportsTo )
SELECT employeeNumber,
managerNumber,
lvl,
city
FROM employee_paths ep
INNER JOIN offices o USING (officeCode)
ORDER BY lvl, city;
讓我們將查詢分解成更小的部分，使其更容易理解。
首先，使用以下查詢形成錨成員：
SELECT
employeeNumber, reportsTo managerNumber, officeCode
FROM
employees
WHERE
reportsTo IS NULL
此查詢(錨成員)返回reportTo為NULL的總經理。
其次，通過引用CTE名稱來執行遞歸成員，在這個示例中為 employee_paths：
SELECT
e.employeeNumber, e.reportsTo, e.officeCode
FROM
employees e
INNER JOIN
employee_paths ep ON ep.employeeNumber = e.reportsTo
此查詢(遞歸成員)返回經理的所有直接上級，直到沒有更多的直接上級。 如果遞歸成員不返回直接上級，則遞歸停止。
第三，使用employee_paths的查詢將CTE返回的結果集與offices表結合起來，以得到最終結果集合。
以下是查詢的輸出：
在本教學中，您已經瞭解了MySQL遞歸CTE以及如何使用它來遍曆分層數據。