前言

在數(shù)據(jù)庫開發(fā)中，我們經(jīng)常會遇到層級數(shù)據(jù)查詢的場景，比如組織架構(gòu)樹、菜單權(quán)限樹、關(guān)聯(lián)關(guān)系鏈等。傳統(tǒng)的查詢方式需要通過多層嵌套或存儲過程循環(huán)實現(xiàn)，代碼繁瑣且性能堪憂。MySQL 8.0 引入的遞歸CTE（公共表表達(dá)式），為層級數(shù)據(jù)查詢提供了簡潔高效的解決方案。本文將從基礎(chǔ)概念出發(fā)，逐步深入遞歸CTE的語法、實戰(zhàn)場景、常見問題與優(yōu)化技巧，幫助你徹底掌握這一強大工具。

一、什么是遞歸CTE？

CTE是一種臨時結(jié)果集，可在SQL語句中多次引用，分為非遞歸CTE和遞歸CTE兩類。其中，遞歸CTE通過WITH RECURSIVE關(guān)鍵字定義，由“初始查詢”和“遞歸查詢”兩部分組成，能夠自動遍歷層級數(shù)據(jù)，直到滿足終止條件。

核心邏輯：遞歸CTE會重復(fù)執(zhí)行“遞歸查詢”，將結(jié)果與“初始查詢”的結(jié)果合并，直到遞歸查詢返回空集，最終輸出合并后的完整結(jié)果集。

二、遞歸CTE基礎(chǔ)語法

遞歸CTE的語法結(jié)構(gòu)嚴(yán)格遵循“初始層 + 遞歸層 + 終止條件”的模式，具體格式如下：

WITH RECURSIVE cte_name (col1, col2, ...) AS (
    -- 1. 初始層（錨點查詢）：定義遞歸的起始數(shù)據(jù)
    SELECT col1, col2, ... FROM table_name WHERE condition
    UNION ALL
    -- 2. 遞歸層：引用CTE自身，定義層級遍歷規(guī)則
    SELECT cte.col1, t.col2, ... 
    FROM cte_name cte
    JOIN table_name t ON cte.colX = t.colY
    WHERE condition -- 3. 終止條件：控制遞歸停止（避免無限遞歸）
)
-- 4. 最終查詢：使用CTE的結(jié)果集
SELECT * FROM cte_name;

關(guān)鍵注意點：初始層和遞歸層返回的字段數(shù)量、字段類型必須完全一致，否則會報語法錯誤。

語法拆解

• CTE名稱與字段：cte_name為CTE的名稱，括號內(nèi)可選指定字段名，若不指定則默認(rèn)使用初始層的字段名。
• 初始層（錨點）：非遞歸查詢，用于定義遞歸的起始數(shù)據(jù)，比如查詢組織架構(gòu)的根節(jié)點。
• UNION ALL：用于合并初始層和遞歸層的結(jié)果集，注意不能用UNION（會去重導(dǎo)致性能下降）。
• 遞歸層：必須引用CTE自身（cte_name），通過關(guān)聯(lián)查詢實現(xiàn)層級遍歷，比如用父節(jié)點ID關(guān)聯(lián)子節(jié)點ID。
• 終止條件：遞歸層的WHERE子句用于控制遞歸停止，比如限制遞歸深度、排除已遍歷的節(jié)點。

三、入門案例：簡單商品分類樹查詢

以電商平臺“商品分類樹”為例，演示遞歸CTE的基礎(chǔ)用法——從根節(jié)點（一級分類）出發(fā)，查詢所有層級的分類信息，包含層級關(guān)系標(biāo)記。

1. 表結(jié)構(gòu)與測試數(shù)據(jù)

-- 商品分類表
CREATE TABLE product_category (
    cat_id INT PRIMARY KEY COMMENT '分類ID',
    cat_name VARCHAR(50) NOT NULL COMMENT '分類名稱',
    parent_cat_id INT COMMENT '父分類ID（根節(jié)點parent_cat_id為0）',
    cat_level INT COMMENT '分類層級（1=一級，2=二級...，可通過遞歸自動計算，此處預(yù)留）',
    is_enable TINYINT DEFAULT 1 COMMENT '是否啟用（1=啟用，0=禁用）'
);
-- 插入測試數(shù)據(jù)（三級分類）
INSERT INTO product_category (cat_id, cat_name, parent_cat_id, cat_level) VALUES
(1, '家用電器', 0, 1),
(2, '手機通訊', 0, 1),
(3, '冰箱', 1, 2),
(4, '空調(diào)', 1, 2),
(5, '智能手機', 2, 2),
(6, '功能手機', 2, 2),
(7, '十字對開門冰箱', 3, 3),
(8, '三門冰箱', 3, 3),
(9, '壁掛式空調(diào)', 4, 3),
(10, '柜式空調(diào)', 4, 3);
-- 商品表（關(guān)聯(lián)分類）
CREATE TABLE product (
    prod_id INT PRIMARY KEY COMMENT '商品ID',
    prod_name VARCHAR(100) NOT NULL COMMENT '商品名稱',
    cat_id INT COMMENT '所屬分類ID',
    price DECIMAL(10,2) COMMENT '商品價格',
    FOREIGN KEY (cat_id) REFERENCES product_category (cat_id)
);
-- 插入測試商品數(shù)據(jù)
INSERT INTO product (prod_id, prod_name, cat_id, price) VALUES
(101, 'XX十字對開門冰箱', 7, 5999.00),
(102, 'XX三門冰箱', 8, 3299.00),
(103, 'XX壁掛式空調(diào)', 9, 2699.00),
(104, 'XX智能手機', 5, 4999.00),
(105, 'XX功能手機', 6, 599.00);

2. 需求：查詢“家用電器”分類下的所有子分類（含層級標(biāo)記）

WITH RECURSIVE category_tree AS (
    -- 初始層：查詢“家用電器”根節(jié)點（cat_id=1，parent_cat_id=0）
    SELECT 
        cat_id,
        cat_name,
        parent_cat_id,
        1 AS current_level, -- 標(biāo)記當(dāng)前層級（一級分類）
        CAST(cat_name AS CHAR(200)) AS cat_path -- 記錄分類路徑（用于展示層級關(guān)系）
    FROM product_category
    WHERE cat_id = 1 -- 起始分類：家用電器
      AND is_enable = 1
    UNION ALL
    -- 遞歸層：查詢子分類，關(guān)聯(lián)父分類ID
    SELECT 
        pc.cat_id,
        pc.cat_name,
        pc.parent_cat_id,
        ct.current_level + 1 AS current_level, -- 層級+1
        CONCAT(ct.cat_path, '→', pc.cat_name) AS cat_path -- 拼接分類路徑
    FROM category_tree ct
    INNER JOIN product_category pc 
        ON ct.cat_id = pc.parent_cat_id -- 父分類ID=子分類父ID
    WHERE pc.is_enable = 1 -- 僅查詢啟用的分類
)
-- 最終查詢：按層級排序，展示分類詳情
SELECT 
    cat_id,
    cat_name,
    parent_cat_id,
    current_level,
    cat_path AS 分類路徑
FROM category_tree
ORDER BY current_level, cat_id;

3. 執(zhí)行結(jié)果

通過遞歸CTE，僅需少量代碼就實現(xiàn)了多層分類的遍歷，同時通過current_level和cat_path清晰標(biāo)記了層級關(guān)系，相比傳統(tǒng)嵌套查詢優(yōu)勢顯著。

四、進(jìn)階實戰(zhàn)：多級分類商品匯總查詢（含統(tǒng)計與篩選）

基于入門案例的商品分類場景，升級為更貼近實戰(zhàn)的需求——從指定分類出發(fā)，遞歸查詢其所有子分類下的商品，同時統(tǒng)計每個分類的商品數(shù)量、最低價格、最高價格，支持按價格區(qū)間篩選商品，最終輸出結(jié)構(gòu)化的分類-商品匯總信息。

1. 核心業(yè)務(wù)需求

• 以“冰箱”分類（cat_id=3）為起點，遞歸查詢其所有子分類（三級分類：十字對開門冰箱、三門冰箱）；
• 匯總每個分類下的商品信息：商品數(shù)量、最低價格、最高價格、平均價格；
• 篩選條件：僅統(tǒng)計價格≥3000元的商品；
• 輸出結(jié)果需包含：分類ID、分類名稱、分類層級、分類路徑、商品統(tǒng)計信息、子分類列表（可選）；
• 處理邊界場景：分類下無符合條件商品時，統(tǒng)計字段顯示0或NULL，并標(biāo)注“無符合條件商品”。

2. 表結(jié)構(gòu)復(fù)用與補充說明

復(fù)用入門案例中的product_category（商品分類表）和product（商品表），無需額外創(chuàng)建表；補充說明：商品表中已包含cat_id（關(guān)聯(lián)分類）和price（價格）字段，可直接用于關(guān)聯(lián)統(tǒng)計。

3. 完整遞歸CTE實現(xiàn)（含統(tǒng)計與篩選）

WITH RECURSIVE category_tree AS (
    -- 步驟1：遞歸查詢“冰箱”分類下的所有子分類（含自身）
    SELECT 
        cat_id,
        cat_name,
        parent_cat_id,
        1 AS current_level,
        CAST(cat_name AS CHAR(200)) AS cat_path,
        CAST(cat_id AS CHAR(100)) AS cat_id_path -- 記錄分類ID路徑，用于后續(xù)子分類列表拼接
    FROM product_category
    WHERE cat_id = 3 -- 起始分類：冰箱
      AND is_enable = 1
    UNION ALL
    SELECT 
        pc.cat_id,
        pc.cat_name,
        pc.parent_cat_id,
        ct.current_level + 1 AS current_level,
        CONCAT(ct.cat_path, '→', pc.cat_name) AS cat_path,
        CONCAT(ct.cat_id_path, ',', pc.cat_id) AS cat_id_path
    FROM category_tree ct
    INNER JOIN product_category pc 
        ON ct.cat_id = pc.parent_cat_id
    WHERE pc.is_enable = 1
),
-- 步驟2：統(tǒng)計每個分類下符合條件的商品信息（價格≥3000元）
product_statistics AS (
    SELECT 
        c.cat_id,
        c.cat_name,
        c.current_level,
        c.cat_path,
        c.cat_id_path,
        COUNT(p.prod_id) AS prod_count, -- 商品數(shù)量
        IFNULL(MIN(p.price), 0) AS min_price, -- 最低價格（無數(shù)據(jù)時為0）
        IFNULL(MAX(p.price), 0) AS max_price, -- 最高價格（無數(shù)據(jù)時為0）
        IFNULL(ROUND(AVG(p.price), 2), 0) AS avg_price, -- 平均價格（保留2位小數(shù)）
        -- 標(biāo)記是否有符合條件的商品
        CASE WHEN COUNT(p.prod_id) > 0 THEN '有符合條件商品' ELSE '無符合條件商品' END AS prod_status
    FROM category_tree c
    LEFT JOIN product p 
        ON c.cat_id = p.cat_id 
        AND p.price >= 3000 -- 篩選條件：價格≥3000元
    GROUP BY c.cat_id, c.cat_name, c.current_level, c.cat_path, c.cat_id_path
),
-- 步驟3：（可選）拼接每個分類的子分類列表（用逗號分隔）
category_child_list AS (
    SELECT 
        parent.cat_id AS parent_cat_id,
        parent.cat_name AS parent_cat_name,
        GROUP_CONCAT(child.cat_id SEPARATOR ',') AS child_cat_ids,
        GROUP_CONCAT(child.cat_name SEPARATOR ',') AS child_cat_names
    FROM category_tree parent
    LEFT JOIN category_tree child 
        ON parent.cat_id = child.parent_cat_id
    GROUP BY parent.cat_id, parent.cat_name
)
-- 步驟4：最終關(guān)聯(lián)查詢，輸出完整結(jié)果
SELECT 
    ps.cat_id,
    ps.cat_name,
    ps.current_level AS 分類層級,
    ps.cat_path AS 分類路徑,
    ps.prod_count AS 商品數(shù)量,
    ps.min_price AS 最低價格,
    ps.max_price AS 最高價格,
    ps.avg_price AS 平均價格,
    ps.prod_status AS 商品狀態(tài),
    IFNULL(ccl.child_cat_ids, '') AS 子分類ID列表,
    IFNULL(ccl.child_cat_names, '') AS 子分類名稱列表
FROM product_statistics ps
LEFT JOIN category_child_list ccl 
    ON ps.cat_id = ccl.parent_cat_id
ORDER BY ps.current_level, ps.cat_id;

4. 代碼分步解析

本次實現(xiàn)采用“多CTE嵌套”模式，將復(fù)雜需求拆分為4個步驟，邏輯清晰且易于維護(hù)：

步驟1：category_tree（遞歸查詢分類樹）

核心功能：從“冰箱”分類（cat_id=3）出發(fā)，遞歸查詢所有子分類，同時記錄current_level（層級）、cat_path（分類名稱路徑）、cat_id_path（分類ID路徑）。其中，cat_id_path用于后續(xù)拼接子分類列表，避免二次遞歸。

步驟2：product_statistics（商品統(tǒng)計）

核心功能：關(guān)聯(lián)分類樹和商品表，按分類分組統(tǒng)計商品信息。關(guān)鍵處理：

• 用LEFT JOIN關(guān)聯(lián)商品表，確保無商品的分類也能被保留；
• 篩選條件p.price ≥ 3000寫在JOIN條件中，而非WHERE子句，避免過濾掉無符合條件商品的分類；
• 用IFNULL函數(shù)處理統(tǒng)計字段的NULL值，確保輸出統(tǒng)一（無數(shù)據(jù)時為0）；
• 通過CASE語句標(biāo)記prod_status，提升結(jié)果可讀性。

步驟3：category_child_list（拼接子分類列表）

核心功能：基于分類樹結(jié)果，按父分類分組，用GROUP_CONCAT拼接子分類的ID和名稱，形成“子分類列表”字段，適配前端下拉選擇或詳情展示需求。

步驟4：最終關(guān)聯(lián)查詢

核心功能：關(guān)聯(lián)統(tǒng)計結(jié)果和子分類列表，輸出完整的業(yè)務(wù)字段，按層級和分類ID排序，確保結(jié)果有序且結(jié)構(gòu)化。

5. 執(zhí)行結(jié)果與驗證

結(jié)果驗證：所有分類均被正確遞歸，統(tǒng)計字段準(zhǔn)確（冰箱分類下2件商品，價格均≥3000元），子分類列表拼接正常，無數(shù)據(jù)字段（如三級分類的子分類列表）顯示為空字符串，符合業(yè)務(wù)需求。

6. 邊界場景測試（無符合條件商品）

若修改篩選條件為“價格≥6000元”，執(zhí)行上述SQL后，結(jié)果如下（關(guān)鍵字段變化）：

邊界場景處理有效：無符合條件商品時，統(tǒng)計字段顯示0，prod_status準(zhǔn)確標(biāo)記，避免了NULL值導(dǎo)致的前端展示問題。

五、常見問題

在使用遞歸CTE時，容易遇到語法錯誤、性能問題、內(nèi)存溢出等問題，以下是高頻問題的解決方案。

1. 語法錯誤：“Recursive query aborted after 1001 iterations”

原因：遞歸深度超過MySQL默認(rèn)限制（默認(rèn)1000層），或存在遞歸閉環(huán)。

解決方案：

• 添加深度限制：在遞歸層添加level <= N（N根據(jù)業(yè)務(wù)調(diào)整，建議不超過100）；
• 阻斷閉環(huán)：添加路徑校驗（如上文的path字段）；
• 臨時調(diào)整配置：SET max_recursion_depth = 5000;（會話級，不建議全局調(diào)整）。

2. 內(nèi)存溢出：“out of memory”

原因：遞歸結(jié)果集過大，或遞歸層關(guān)聯(lián)過多表導(dǎo)致內(nèi)存占用激增。

解決方案：

• 提前過濾：遞歸層僅保留必要字段，過濾NULL、空字符串等無效數(shù)據(jù)；
• 延遲關(guān)聯(lián)：將非必要的多表關(guān)聯(lián)（如名稱補全、HIT關(guān)聯(lián)）延遲到最終處理層，減少遞歸層數(shù)據(jù)量；
• 改用臨時表：若結(jié)果集極大，用“臨時表 + 循環(huán)”替代遞歸CTE，將數(shù)據(jù)寫入磁盤而非內(nèi)存。

3. 性能低下：全表掃描

原因：關(guān)聯(lián)字段未加索引，導(dǎo)致遞歸層每次查詢都全表掃描。

解決方案：

• 給關(guān)聯(lián)字段加索引：如B表的source_id、rel_type字段，添加復(fù)合索引idx_b_source_rel (source_id, rel_type)；
• 覆蓋索引：將遞歸層需要的字段（如rel_id、rel_name）納入索引，減少回表查詢。

4. 字段對齊錯誤：“Column count doesn’t match value count at row 1”

原因：初始層和遞歸層返回的字段數(shù)量或類型不一致。

解決方案：

• 嚴(yán)格檢查兩層查詢的字段數(shù)量，確保完全一致；
• 統(tǒng)一字段類型：比如初始層level為INT，遞歸層也必須是INT。

六、執(zhí)行步驟及生命周期

1.執(zhí)行時序

執(zhí)行時序圖展示了CTE從查詢提交到結(jié)果返回的完整交互流程：客戶端發(fā)送查詢后，查詢優(yōu)化器解析并生成執(zhí)行計劃，執(zhí)行引擎首先執(zhí)行錨點查詢生成初始結(jié)果集，隨后驅(qū)動遞歸查詢進(jìn)行迭代循環(huán)，每次迭代基于前次結(jié)果生成新數(shù)據(jù)集并檢查終止條件，最終通過臨時表管理器合并所有結(jié)果集，應(yīng)用去重操作后返回給客戶端。

2.生命周期

遞歸CTE生命周期圖中以狀態(tài)機形式描述了查詢從解析到結(jié)束的完整狀態(tài)流轉(zhuǎn)：始于查詢解析階段的語法驗證，經(jīng)過查詢優(yōu)化生成執(zhí)行計劃，進(jìn)入初始化執(zhí)行準(zhǔn)備臨時數(shù)據(jù)，核心階段是遞歸循環(huán)執(zhí)行，通過迭代計數(shù)器控制遞歸深度并檢查終止條件，最后在結(jié)果合并處理階段聚合所有中間數(shù)據(jù)，完成格式化后返回最終結(jié)果，整個生命周期清晰劃分了解析、優(yōu)化、執(zhí)行和結(jié)果處理四個關(guān)鍵階段。

七、遞歸CTE適用場景與局限性

1. 適用場景

• 層級數(shù)據(jù)查詢：組織架構(gòu)、菜單樹、分類樹等；
• 關(guān)聯(lián)關(guān)系遍歷：如本文的多類型關(guān)聯(lián)鏈查詢；
• 數(shù)字序列生成：如生成1-100的連續(xù)數(shù)字（WITH RECURSIVE nums AS (SELECT 1 n UNION ALL SELECT n+1 FROM nums WHERE n<100)）。

2. 局限性

• 版本限制：僅支持MySQL 8.0+，5.x版本需用存儲過程替代；
• 內(nèi)存依賴：結(jié)果集過大會導(dǎo)致內(nèi)存溢出，不適合超大規(guī)模層級數(shù)據(jù)；
• 復(fù)雜度高：多表關(guān)聯(lián)+復(fù)雜邏輯時，可讀性和維護(hù)成本上升。

總結(jié)

MySQL遞歸CTE通過“初始層 + 遞歸層”的簡潔結(jié)構(gòu)，徹底解決了傳統(tǒng)層級查詢的繁瑣問題，大幅提升了代碼可讀性和開發(fā)效率。在實際應(yīng)用中，需重點關(guān)注“閉環(huán)防護(hù)”“深度控制”“索引優(yōu)化”三個核心點，避免出現(xiàn)性能和內(nèi)存問題。

對于簡單層級查詢，遞歸CTE是最優(yōu)選擇；對于超大規(guī)模或極端復(fù)雜的場景，可結(jié)合臨時表、緩存等方案優(yōu)化。掌握遞歸CTE的核心邏輯和避坑技巧，能讓你在處理層級數(shù)據(jù)時游刃有余。

小提示：使用遞歸CTE時，建議先通過SELECT COUNT(*)測試結(jié)果集大小，再逐步完善業(yè)務(wù)邏輯，避免直接執(zhí)行導(dǎo)致內(nèi)存溢出。

到此這篇關(guān)于MySQL遞歸CTE的文章就介紹到這了,更多相關(guān)MySQL遞歸CTE內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論