快捷導(dǎo)航

mysql查詢樹形,id與pid關(guān)聯(lián)實(shí)踐

更新時(shí)間：2026年06月05日 10:04:03 作者：hefeng_aspnet

在MySQL中查詢基于id和pid關(guān)聯(lián)的樹形結(jié)構(gòu)數(shù)據(jù),推薦使用遞歸公用表表達(dá)式（WITH RECURSIVE）以實(shí)現(xiàn)高效遞歸查詢,適用于MySQL8.0及以上版本,對(duì)于舊版本MySQL,可使用自定義函數(shù)收集所有節(jié)點(diǎn)或父節(jié)點(diǎn)ID,或采用應(yīng)用層組裝策略,一次性加載數(shù)據(jù)并內(nèi)存組裝樹形結(jié)構(gòu)

1. 使用遞歸公用表表達(dá)式（推薦 MySQL 8.0+）

這是最標(biāo)準(zhǔn)且性能較好的方式，利用 WITH RECURSIVE語(yǔ)法實(shí)現(xiàn)遞歸查詢。

‌查詢指定節(jié)點(diǎn)的所有子級(jí)（向下查詢）：

WITH RECURSIVE tree_cte AS (
    -- 錨點(diǎn)成員：指定起始節(jié)點(diǎn)
    SELECT id, pid, name, 1 as level
    FROM tree
    WHERE id = 1  -- 替換為指定的父節(jié)點(diǎn)ID
    UNION ALL
    -- 遞歸成員：查找子節(jié)點(diǎn)
    SELECT t.id, t.pid, t.name, tc.level + 1
    FROM tree t
    INNER JOIN tree_cte tc ON t.pid = tc.id
)
SELECT * FROM tree_cte;

查詢指定節(jié)點(diǎn)的所有父級(jí)（向上查詢）：

WITH RECURSIVE parent_cte AS (
    -- 錨點(diǎn)成員：指定起始節(jié)點(diǎn)
    SELECT id, pid, name
    FROM tree
    WHERE id = 5  -- 替換為指定的子節(jié)點(diǎn)ID
    UNION ALL
    -- 遞歸成員：查找父節(jié)點(diǎn)
    SELECT t.id, t.pid, t.name
    FROM tree t
    INNER JOIN parent_cte pc ON t.id = pc.pid
)
SELECT * FROM parent_cte;

2. 使用自定義函數(shù)（適用于 MySQL 5.7及以下）

在舊版本MySQL中，通常通過創(chuàng)建存儲(chǔ)函數(shù)，利用循環(huán)和 FIND_IN_SET 或 GROUP_CONCAT 來收集所有子節(jié)點(diǎn)或父節(jié)點(diǎn)的ID字符串，然后再進(jìn)行查詢。

‌獲取所有子節(jié)點(diǎn)ID的函數(shù)示例邏輯：‌

初始化一個(gè)包含根節(jié)點(diǎn)ID的字符串變量。
循環(huán)查詢當(dāng)前層級(jí)所有節(jié)點(diǎn)的子節(jié)點(diǎn)ID。
將新發(fā)現(xiàn)的子節(jié)點(diǎn)ID拼接到字符串中。
直到?jīng)]有新的子節(jié)點(diǎn)產(chǎn)生為止。
返回ID字符串，外層使用 FIND_IN_SET(id, get_child_ids(root_id)) 進(jìn)行過濾。

注意：這種方法在數(shù)據(jù)量較大時(shí)性能較差，且受限于 group_concat_max_len 系統(tǒng)變量。

3. 應(yīng)用層組裝（通用方案）

如果數(shù)據(jù)庫(kù)查詢壓力較大或邏輯復(fù)雜，可以采用“一次加載，內(nèi)存組裝”的策略：

‌3.1 SQL查詢‌：一次性查詢出所有相關(guān)節(jié)點(diǎn)（或全表），按 pid 或 level 排序。

SELECT * FROM tree ORDER BY pid, id;

‌3.2 代碼處理‌：在Java/Python/Go等后端語(yǔ)言中，遍歷列表，利用Map將節(jié)點(diǎn)掛載到對(duì)應(yīng)的父節(jié)點(diǎn)下。

創(chuàng)建一個(gè) Map<ID, Node>。
遍歷所有節(jié)點(diǎn)，將節(jié)點(diǎn)放入Map。
再次遍歷，根據(jù) pid 從Map中找到父節(jié)點(diǎn)，將當(dāng)前節(jié)點(diǎn)加入父節(jié)點(diǎn)的 children 列表。
篩選出根節(jié)點(diǎn)（pid為0或null）作為樹的入口。

4. 其他高級(jí)設(shè)計(jì)模式（針對(duì)高性能需求）

如果樹形結(jié)構(gòu)非常深且查詢頻繁，可以考慮改變表結(jié)構(gòu)設(shè)計(jì)：

‌路徑枚舉（Path Enumeration）‌：增加 path 字段（如 0,1,5,10），查詢子節(jié)點(diǎn)時(shí)使用 LIKE '0,1,%'。
閉包表（Closure Table）‌：新建一張關(guān)系表，存儲(chǔ)所有祖先-后代關(guān)系對(duì)，查詢時(shí)直接Join該表，效率極高但維護(hù)成本稍高。
嵌套集（MPTT）‌：增加 lft 和 rgt 字段，通過范圍查詢獲取子樹，讀取性能極佳，但插入和移動(dòng)節(jié)點(diǎn)時(shí)需要更新大量數(shù)據(jù)。