MYSQL中WITH RECURSIVE遞歸查詢的實現(xiàn)
更新時間：2025年08月26日 09:45:53 作者：五月天的尾巴
MySQL 8.0引入WITH RECURSIVE遞歸查詢,用于處理層級數(shù)據(jù),本文主要介紹了MYSQL中WITH RECURSIVE遞歸查詢的實現(xiàn),具有一定的參考價值,感興趣的可以了解一下
一、前言
從MySQL 8.0開始，可以使用WITH RECURSIVE來創(chuàng)建遞歸公用表表達式（Common Table Expressions , CTE）。
遞歸查詢在各種應用場景中都很常見，例如：
- 組織架構查詢： 獲取公司內部的組織結構信息，從CEO到普通員工的層級關系。
- 產品分類查詢： 從頂級分類到子分類的層級查找。
- 目錄結構查詢： 在文件系統(tǒng)中，從根目錄到子目錄的路徑查找。
二、語法
WITH RECURSIVE cte_name (column_list) AS ( SELECT initial_query -- 初始查詢 UNION [ALL] recursive_query -- 遞歸查詢 ) SELECT * FROM cte_name;
其中：
CTE名稱（cte_name）用于標識遞歸查詢的臨時結果集。- 列名列表（
column_list）定義了 CTE 結果集中包含的列及其名稱。 - 初始查詢（
initial_query）提供遞歸過程的起點，即第一次迭代時使用的數(shù)據(jù)。 - 遞歸部分（遞歸子查詢）定義了如何將前一次迭代的結果作為輸入，計算出下一次迭代的數(shù)據(jù)。
recursive_query:表示遞歸查詢語句，應當與column_list中的列名對應。SELECT * FROM cte_name：表示最終返回的查詢結果集，可以通過cte_name查詢表中的列名進行指定。
1.遞歸查詢的結構
遞歸查詢通常由兩部分構成：初始化查詢（非遞歸部分）和遞歸子查詢（遞歸部分）。
- 初始化查詢： 定義遞歸開始時的基礎數(shù)據(jù)集，通常是與遞歸邏輯相關的最頂層數(shù)據(jù)或邊界條件。
- 遞歸子查詢： 定義如何根據(jù)前一次迭代的結果生成下一次迭代的數(shù)據(jù)。遞歸子查詢通常包含對自身 CTE 名稱的引用，以遞歸地應用相同的操作。
2.連接操作符：
- 遞歸查詢的
初始化查詢和遞歸查詢通常通過UNION或UNION ALL連接起來，形成一個完整的遞歸查詢表達式。 UNION會去除結果集中的重復行，而UNION ALL不會去除重復，根據(jù)實際需求選擇合適的連接操作符。
3.終止條件
- 遞歸查詢必須有一個明確的終止條件，否則會無限循環(huán)下去。終止條件通常隱含在遞歸子查詢的
WHERE子句或其他邏輯中，當滿足特定條件時，不再產生新的結果。
三、示例
通過以下目錄樹示例查詢父節(jié)點、子節(jié)點、及全鏈路等信息。
3.1、創(chuàng)建測試表和數(shù)據(jù)
CREATE TABLE tree_table (
id INT PRIMARY KEY,
name VARCHAR(50),
parent_id INT
);
INSERT INTO tree_table (id, name, parent_id) VALUES
(1, 'A', NULL),
(2, 'B', 1),
(3, 'C', 1),
(4, 'D', 2),
(5, 'E', 3);
(6, 'F', 3);
(7, 'G', 5);
3.2、查詢所有子節(jié)點（以 id=1 為例）
WITH RECURSIVE cte AS (
SELECT id, parent_id, name
FROM tree_table
WHERE id = 1
UNION ALL
SELECT t.id, t.parent_id, t.name
FROM tree_table t
INNER JOIN cte ON t.parent_id = cte.id
)
SELECT * FROM cte;
結果：
3.3、查詢所有父節(jié)點（以 id=5 為例）
WITH RECURSIVE cte AS (
SELECT id, parent_id, name
FROM tree_table
WHERE id = 5
UNION ALL
SELECT t.id, t.parent_id, t.name
FROM tree_table t
INNER JOIN cte ON t.id = cte.parent_id
)
SELECT * FROM cte;
注意：子節(jié)點與父節(jié)點的區(qū)別在于join的關聯(lián)條件on的不同。
結果：
3.4、查詢所有子節(jié)點（向下遞歸）并顯示全鏈路路徑及層級
查詢節(jié)點下的所有子節(jié)點，并且可以看到全鏈路及層級，如A->B->C
WITH RECURSIVE cte AS (
SELECT
id,parent_id, name,
name AS path, -- 初始路徑
1 AS level -- 起始層級
FROM tree_table
WHERE id = 1 -- 查詢ID=1的所有子節(jié)點
UNION ALL
SELECT
t.id,t.parent_id,t.name,
CONCAT(cte.path, '->', t.name), -- 路徑拼接
cte.level + 1 -- 層級遞增
FROM tree_table t
INNER JOIN cte ON t.parent_id = cte.id
)
SELECT * FROM cte;
結果如下：
3.5、查詢所有父節(jié)點（向上遞歸）并顯示全鏈路路徑及層級
查詢節(jié)點的所有父節(jié)點，并且可以看到全鏈路及層級，如A->B->C
WITH RECURSIVE cte AS (
SELECT
id, parent_id, name,
CAST(name AS CHAR(255)) AS path, -- 路徑初始化
1 AS level -- 起始層級
FROM tree_table
WHERE id = 6 -- 查詢ID=6的所有父節(jié)點
UNION ALL
SELECT
t.id, t.parent_id, t.name,
CONCAT(t.name, '->', cte.path), -- 向前拼接路徑
cte.level + 1 -- 層級遞增
FROM tree_table t
INNER JOIN cte ON t.id = cte.parent_id
)
SELECT * FROM cte
ORDER BY level ASC; -- 按層級升序排列
結果如下：
3.6、高級功能：雙向遞歸查詢（同時獲取祖先和后代）
-- 獲取ID=5的所有祖先和后代
WITH RECURSIVE
down AS ( /* 向下遞歸獲取后代 */
SELECT id, parent_id, name, name AS path, 1 AS level
FROM tree_table WHERE id = 5
UNION ALL
SELECT t.id, t.parent_id, t.name,
CONCAT(down.path, '->', t.name),
down.level + 1
FROM tree_table t
INNER JOIN down ON t.parent_id = down.id
),
up AS ( /* 向上遞歸獲取祖先 */
SELECT id, parent_id, name, name AS path, 1 AS level
FROM tree_table WHERE id = 5
UNION ALL
SELECT t.id, t.parent_id, t.name,
CONCAT(t.name, '->', up.path),
up.level + 1
FROM tree_table t
INNER JOIN up ON t.id = up.parent_id
)
SELECT * FROM up WHERE id != 5 -- 排除重復的當前節(jié)點
UNION ALL
SELECT * FROM down;
結果如下：
上圖示例中同時查詢了父節(jié)點與子節(jié)點，可能從返回結果中不能區(qū)別出哪些是父節(jié)點哪些是子節(jié)點。這里改造一下：
-- 獲取ID=5的所有祖先和后代
WITH RECURSIVE
down AS ( /* 向下遞歸獲取后代 */
SELECT id, parent_id, name, name AS path, 1 AS level,'down' as type
FROM tree_table WHERE id = 5
UNION ALL
SELECT t.id, t.parent_id, t.name,
CONCAT(down.path, '->', t.name),
down.level + 1,
'down' as type
FROM tree_table t
INNER JOIN down ON t.parent_id = down.id
),
up AS ( /* 向上遞歸獲取祖先 */
SELECT id, parent_id, name, name AS path, 1 AS level,'up' as type
FROM tree_table WHERE id = 5
UNION ALL
SELECT t.id, t.parent_id, t.name,
CONCAT(t.name, '->', up.path),
up.level + 1,
'up' as type
FROM tree_table t
INNER JOIN up ON t.id = up.parent_id
)
SELECT * FROM up WHERE id != 5 -- 排除重復的當前節(jié)點
UNION ALL
SELECT * FROM down;
添加type字段區(qū)分父節(jié)點、子節(jié)點。
四、擴展
4.1、注意事項：
- 遞歸深度限制： MySQL 默認遞歸深度為 1000，超限會報錯?？赏ㄟ^設置會話變量調整：
SET SESSION cte_max_recursion_depth = 10000; -- 修改遞歸深度
避免死循環(huán)： 確保數(shù)據(jù)中沒有循環(huán)引用（如 A→B→A）。
性能： 大數(shù)據(jù)量時確保 parent_id 字段有索引：
CREATE INDEX idx_parent_id ON tree_table(parent_id);
- 路徑長度限制：
CAST(name AS CHAR(255)) -- 根據(jù)實際需要調整長度
4.2、避免死循環(huán)
避免死循環(huán)： 確保數(shù)據(jù)中沒有循環(huán)引用（如 A→B→A）
如果遇到循環(huán)引用會導致遞歸查詢無限循環(huán)查詢，從而引發(fā)數(shù)據(jù)庫異常報錯。
解決方法：
- 方法一：避免死循環(huán)，確保數(shù)據(jù)中沒有循環(huán)引用（如
A→B→A） - 方法二：限制遞歸層級，查詢時設置遞歸層級，如小于10級
4.3、結果排序技巧
- 按層級排序：
ORDER BY level ASC - 按路徑排序：
ORDER BY full_path ASC - 按樹形結構排序：
ORDER BY LENGTH(full_path) - LENGTH(REPLACE(full_path, '->', '')), full_path
到此這篇關于MYSQL中WITH RECURSIVE遞歸查詢的實現(xiàn)的文章就介紹到這了,更多相關MYSQL WITH RECURSIVE遞歸查詢內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
MySQL Server維護了幾個時區(qū),本文主要介紹了MySQL Server時區(qū)支持的使用,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧2024-07-07
mysql 數(shù)據(jù)庫鏈接狀態(tài)確認實驗(推薦)
這篇文章主要介紹了mysql 數(shù)據(jù)庫鏈接狀態(tài)確認實驗,通過本文我選擇 了三種方案給大家詳細講解，結合實例代碼給大家介紹的非常詳細，需要的朋友可以參考下2022-09-09
這篇文章主要介紹了mysql 登錄時閃退的問題解決方法的相關資料,mysql 出現(xiàn)閃退問題，很是棘手在做項目的時候，這里對解決這樣的問題提供了解決方案，需要的朋友可以參考下2016-11-11
這篇文章主要給大家介紹了關于MySQL表的增刪改查的相關資料，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2021-04-04
mysql5.5 master-slave(Replication)主從配置
在主機master中對test數(shù)據(jù)庫進行sql操作，再查看從機test數(shù)據(jù)庫是否產生同步。2011-07-07
這篇文章主要介紹了Windows下使用Cygwin編譯MySQL客戶端,本文詳細的講解了編譯過程,需要的朋友可以參考下2015-04-04
MySQL實現(xiàn)定時自動備份的流程步驟(Windows環(huán)境)
這篇文章主要介紹了MySQL實現(xiàn)定時自動備份的流程步驟(Windows環(huán)境),文中通過圖文結合的方式介紹的非常詳細,對大家的學習或工作有一定的幫助,需要的朋友可以參考下2024-12-12
MySQL數(shù)據(jù)庫如何開啟遠程連接(多備份)
多備份服務器在備份你的數(shù)據(jù)庫時，必須能夠遠程連接上你的數(shù)據(jù)庫。但是一般來說mysql安裝時都是關閉遠程連接的，因此，需要你開通mysql數(shù)據(jù)庫的遠程訪問權限。那么如何開啟呢2015-01-01
這篇文章主要介紹了MySQL中的排序規(guī)則,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-11-11
如何使用MySQL?Explain?分析?SQL?執(zhí)行計劃
MySQL?提供的?EXPLAIN?工具能夠幫助我們深入了解查詢語句的執(zhí)行過程、索引使用情況以及潛在的性能瓶頸,本文將詳細介紹如何使用?EXPLAIN?分析?SQL?執(zhí)行計劃,并探討其中各個重要字段的含義以及優(yōu)化建議,感興趣的朋友一起看看吧2025-04-04