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）。
遞歸查詢在各種應用場景中都很常見，例如：
- 組織架構查詢： 獲取公司內(nèi)部的組織結(jié)構信息，從CEO到普通員工的層級關系。
- 產(chǎn)品分類查詢： 從頂級分類到子分類的層級查找。
- 目錄結(jié)構查詢： 在文件系統(tǒng)中，從根目錄到子目錄的路徑查找。
二、語法
WITH RECURSIVE cte_name (column_list) AS ( SELECT initial_query -- 初始查詢 UNION [ALL] recursive_query -- 遞歸查詢 ) SELECT * FROM cte_name;
其中：
CTE名稱（cte_name）用于標識遞歸查詢的臨時結(jié)果集。- 列名列表（
column_list）定義了 CTE 結(jié)果集中包含的列及其名稱。 - 初始查詢（
initial_query）提供遞歸過程的起點，即第一次迭代時使用的數(shù)據(jù)。 - 遞歸部分（遞歸子查詢）定義了如何將前一次迭代的結(jié)果作為輸入，計算出下一次迭代的數(shù)據(jù)。
recursive_query:表示遞歸查詢語句，應當與column_list中的列名對應。SELECT * FROM cte_name：表示最終返回的查詢結(jié)果集，可以通過cte_name查詢表中的列名進行指定。
1.遞歸查詢的結(jié)構
遞歸查詢通常由兩部分構成：初始化查詢（非遞歸部分）和遞歸子查詢（遞歸部分）。
- 初始化查詢： 定義遞歸開始時的基礎數(shù)據(jù)集，通常是與遞歸邏輯相關的最頂層數(shù)據(jù)或邊界條件。
- 遞歸子查詢： 定義如何根據(jù)前一次迭代的結(jié)果生成下一次迭代的數(shù)據(jù)。遞歸子查詢通常包含對自身 CTE 名稱的引用，以遞歸地應用相同的操作。
2.連接操作符：
- 遞歸查詢的
初始化查詢和遞歸查詢通常通過UNION或UNION ALL連接起來，形成一個完整的遞歸查詢表達式。 UNION會去除結(jié)果集中的重復行，而UNION ALL不會去除重復，根據(jù)實際需求選擇合適的連接操作符。
3.終止條件
- 遞歸查詢必須有一個明確的終止條件，否則會無限循環(huán)下去。終止條件通常隱含在遞歸子查詢的
WHERE子句或其他邏輯中，當滿足特定條件時，不再產(chǎn)生新的結(jié)果。
三、示例
通過以下目錄樹示例查詢父節(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;
結(jié)果：
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的不同。
結(jié)果：
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;
結(jié)果如下：
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; -- 按層級升序排列
結(jié)果如下：
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é)點與子節(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，超限會報錯。可通過設置會話變量調(diào)整：
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ù)實際需要調(diào)整長度
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、結(jié)果排序技巧
- 按層級排序：
ORDER BY level ASC - 按路徑排序：
ORDER BY full_path ASC - 按樹形結(jié)構排序：
ORDER BY LENGTH(full_path) - LENGTH(REPLACE(full_path, '->', '')), full_path
到此這篇關于MYSQL中WITH RECURSIVE遞歸查詢的實現(xiàn)的文章就介紹到這了,更多相關MYSQL WITH RECURSIVE遞歸查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
mysql Community Server 5.7.19安裝指南（詳細）
這篇文章主要介紹了mysql Community Server 5.7.19安裝指南（詳細）,需要的朋友可以參考下2017-10-10
本文詳細介紹了MySQL在線DDL操作的機制,包括不同版本的影響、索引類型和操作方式對鎖表的影響,以及如何查看和監(jiān)控DDL操作的鎖機制,通過實際案例和最佳實踐,提供了在生產(chǎn)環(huán)境中高效執(zhí)行DDL操作的建議,感興趣的朋友跟隨小編一起看看吧2026-01-01
在 MySQL 中,NULL 值和空值是兩個不同的概念,本文主要介紹了MySQL中空值和NULL的區(qū)別小結(jié),具有一定的參考價值,感興趣的可以了解一下2024-08-08
這篇文章主要為大家詳細介紹了Linux安裝MySQL教程，二進制分發(fā)版，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-02-02
MySQL主從同步診斷show?slave?status?卡住的深度排查與解決方案(最新推薦)
在?MySQL主從架構的日常運維中,show?slave?status是監(jiān)控同步狀態(tài)的核心命令,本文結(jié)合源碼分析與調(diào)試實踐,揭示其背后的鎖競爭機制,并提供系統(tǒng)性的排查與優(yōu)化方案,感興趣的朋友一起看看吧2025-06-06
這篇文章主要為大家詳細介紹了mysql實現(xiàn)游標分頁的相關方法,文中的示例代碼講解詳細,具有一定的借鑒價值,感興趣的小伙伴可以跟隨小編一起學習一下2025-10-10
mysql千萬級數(shù)據(jù)分頁查詢性能優(yōu)化
本文給大家分享的是作者在使用mysql進行千萬級數(shù)據(jù)量分頁查詢的時候進行性能優(yōu)化的方法，非常不錯的一篇文章，對我們學習mysql性能優(yōu)化非常有幫助2017-11-11
Navicat導入導出Mysql?結(jié)構、數(shù)據(jù)、結(jié)構+數(shù)據(jù)完整步驟
導出表結(jié)構是指將數(shù)據(jù)庫中的表的結(jié)構信息導出為SQL腳本或其他格式的文件,這個功能非常實用,特別是在數(shù)據(jù)庫遷移或備份時,這篇文章主要給大家介紹了關于Navicat導入導出Mysql?結(jié)構、數(shù)據(jù)、結(jié)構+數(shù)據(jù)的相關資料,需要的朋友可以參考下2024-08-08
在寫報表功能時遇到一個需要根據(jù)用戶id分組查詢最新一條錢包明細數(shù)據(jù)的需求,下面這篇文章主要給大家總結(jié)介紹了關于Mysql分組查詢?nèi)∽钚碌膸追N方案,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下2023-05-05
教你自動恢復MySQL數(shù)據(jù)庫的日志文件(binlog)
如果MySQL服務器啟用了二進制日志，你可以使用mysqlbinlog工具來恢復從指定的時間點開始2014-05-05