mysql遞歸查詢語法WITH RECURSIVE的使用

更新時間：2025年05月09日 10:23:39 作者：小玉起起

本文主要介紹了mysql遞歸查詢語法WITH RECURSIVE的使用,WITH RECURSIVE用于執(zhí)行遞歸查詢,特別適合處理層級結(jié)構(gòu)或遞歸數(shù)據(jù),具有一定的參考價值,感興趣的可以了解一下

WITH RECURSIVE 是 SQL 中用于執(zhí)行遞歸查詢的語法，特別適合于處理層級結(jié)構(gòu)或遞歸數(shù)據(jù)（如樹形結(jié)構(gòu)、圖結(jié)構(gòu)）。遞歸查詢可以反復引用自己來查詢多層次的數(shù)據(jù)，而無需寫多個嵌套查詢。

基本語法結(jié)構(gòu)：

WITH RECURSIVE CTE_name AS (
    -- 基礎查詢部分 (非遞歸部分)
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition

    UNION ALL

    -- 遞歸查詢部分
    SELECT column1, column2, ...
    FROM table_name t
    JOIN CTE_name cte ON t.column = cte.column
    WHERE condition
)
SELECT * FROM CTE_name;

關鍵部分解析：

WITH RECURSIVE：
- WITH 用于創(chuàng)建公共表表達式（CTE），RECURSIVE 關鍵字標識這是一個遞歸查詢。
- CTE_name 是你為公共表表達式（CTE）起的名字，后續(xù)查詢可以引用它。
基礎查詢（非遞歸部分）：
- 這是遞歸查詢的起點，用于查詢層級結(jié)構(gòu)中的根數(shù)據(jù)（通常是最上層或最初始的數(shù)據(jù)）。
- 通常，這部分查詢會返回一個起始集合或基礎條件，如樹形結(jié)構(gòu)中的根節(jié)點。
遞歸查詢部分：
- 遞歸查詢部分通常會參考（引用）上面基礎查詢的結(jié)果，形成一個不斷迭代的過程。
- 在遞歸查詢部分中，常常會使用 JOIN 或者自連接來與 CTE_name（即遞歸查詢的結(jié)果集）進行連接，查找下級數(shù)據(jù)。
- 遞歸查詢會逐步深入，直到?jīng)]有更多的數(shù)據(jù)為止。
UNION ALL：
- UNION ALL 用于將基礎查詢（非遞歸部分）和遞歸查詢部分合并成一個完整的結(jié)果集。
- UNION ALL 不會去重（不同于 UNION），通常用于遞歸查詢，以保持所有結(jié)果。
最終查詢：
- 查詢 CTE_name，得到遞歸查詢的最終結(jié)果。
- 遞歸查詢的結(jié)果會返回所有層次的數(shù)據(jù)，直到?jīng)]有更多的層級為止。

遞歸查詢的工作流程：

第一次迭代：
- 執(zhí)行基礎查詢部分，返回初始的數(shù)據(jù)集（通常是最頂層的數(shù)據(jù)）。
第二次及后續(xù)迭代：
- 遞歸查詢部分會基于前一次查詢的結(jié)果繼續(xù)進行，查找下一級的數(shù)據(jù)（比如查找所有根節(jié)點的子節(jié)點）。
- 每一輪迭代都會向結(jié)果集中添加新的行。
停止條件：
- 當遞歸查詢找不到更多符合條件的行時，遞歸查詢停止，返回最終的結(jié)果。

示例：員工與經(jīng)理的層級關系

假設有一個員工表，每個員工有一個 manager_id 字段指向他們的經(jīng)理，我們希望查詢某個員工及其所有上級經(jīng)理，直到最頂層的經(jīng)理為止。

WITH RECURSIVE EmployeeHierarchy AS (
    -- 基礎查詢部分：查找某個特定員工
    SELECT id, name, manager_id
    FROM employees
    WHERE id = :employee_id  -- 查找指定員工

    UNION ALL

    -- 遞歸查詢部分：查找員工的經(jīng)理
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN EmployeeHierarchy eh ON e.id = eh.manager_id
)
-- 返回所有員工及其上級經(jīng)理
SELECT * FROM EmployeeHierarchy;

解釋：

基礎查詢部分：
- 查找特定員工（通過 id = :employee_id）。
遞歸查詢部分：
- 通過自連接 JOIN EmployeeHierarchy eh ON e.id = eh.manager_id 查找該員工的經(jīng)理（manager_id 字段指向的員工）。
UNION ALL：
- 合并基礎查詢部分（初始員工）和遞歸查詢部分（逐級向上查找經(jīng)理）。
查詢最終結(jié)果：
- 返回遞歸查詢的結(jié)果，即該員工及其所有上級經(jīng)理。

示例：樹形結(jié)構(gòu)的數(shù)據(jù)（如分類）

假設有一個包含分類的表 categories，每個分類有一個 parent_id 字段指向其父分類。我們希望查詢某個分類及其所有的子分類。

WITH RECURSIVE CategoryHierarchy AS (
    -- 基礎查詢部分：查找某個特定分類
    SELECT id, name, parent_id
    FROM categories
    WHERE id = :category_id  -- 查找指定分類

    UNION ALL

    -- 遞歸查詢部分：查找分類的子分類
    SELECT c.id, c.name, c.parent_id
    FROM categories c
    JOIN CategoryHierarchy ch ON c.parent_id = ch.id
)
-- 返回所有分類及其子分類
SELECT * FROM CategoryHierarchy;

解釋：

基礎查詢部分：
- 查找指定的分類（通過 id = :category_id）。
遞歸查詢部分：
- 查找所有子分類，JOIN 操作通過 c.parent_id = ch.id 來連接父分類和子分類。
UNION ALL：
- 合并基礎查詢和遞歸查詢部分，逐層查找所有子分類。

遞歸查詢的特性：

遞歸深度限制：
- 大多數(shù)數(shù)據(jù)庫系統(tǒng)（如 PostgreSQL、MySQL 等）會對遞歸查詢的深度進行限制，防止無限遞歸。MySQL 默認為 1000 層深度，但可以通過配置來調(diào)整此值。
性能問題：
- 遞歸查詢可能會消耗較多的資源，特別是當層級較多或數(shù)據(jù)量龐大時。需要小心使用，避免導致性能瓶頸。
迭代過程：
- 遞歸查詢通過每一輪的迭代逐步向下查詢，直到?jīng)]有更多數(shù)據(jù)。每一輪迭代的結(jié)果都會在下次查詢中被引用。

總結(jié)：

WITH RECURSIVE 適用于處理層級結(jié)構(gòu)或遞歸關系的數(shù)據(jù)，允許在查詢中反復引用自己，查找多層次的數(shù)據(jù)。
它由基礎查詢（非遞歸部分）和遞歸查詢部分組成，通過 UNION ALL 連接兩部分，逐步展開結(jié)果。
使用遞歸查詢時，需要注意遞歸深度限制和性能影響。

到此這篇關于mysql遞歸查詢語法WITH RECURSIVE的使用的文章就介紹到這了,更多相關mysql WITH RECURSIVE內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: