MySQL 樹形結構遞歸遍歷：存儲過程實現多級選單與部門查詢

MySQL 樹形結構遞歸遍歷：存儲過程實現多級選單與部門查詢

MySQL 缺乏 Oracle 的 CONNECT BY，處理多級選單或部門樹形查詢較為棘手。本文通過臨時表 + 遞歸存儲過程實現向下遍歷子節點、向上追溯父節點、生成路徑字符串等功能。

測試表結構

CREATE TABLE channel (
    id INT PRIMARY KEY AUTO_INCREMENT,
    cname VARCHAR(200),
    parent_id INT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

核心存儲過程

1. 向下遞歸生成子節點列表

CREATE PROCEDURE pro_cre_childlist(IN rootId INT, IN nDepth INT)
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE b INT;
    DECLARE cur CURSOR FOR SELECT id FROM channel WHERE parent_id = rootId;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    SET max_sp_recursion_depth = 12;
    INSERT INTO tmpLst VALUES (NULL, rootId, nDepth);
    OPEN cur;
    FETCH cur INTO b;
    WHILE done = 0 DO
        CALL pro_cre_childlist(b, nDepth + 1);
        FETCH cur INTO b;
    END WHILE;
    CLOSE cur;
END

2. 向上遞歸追溯父節點

CREATE PROCEDURE pro_cre_parentlist(IN rootId INT, IN nDepth INT)
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE b INT;
    DECLARE cur CURSOR FOR SELECT parent_id FROM channel WHERE id = rootId;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    SET max_sp_recursion_depth = 12;
    INSERT INTO tmpLst VALUES (NULL, rootId, nDepth);
    OPEN cur;
    FETCH cur INTO b;
    WHILE done = 0 DO
        CALL pro_cre_parentlist(b, nDepth + 1);
        FETCH cur INTO b;
    END WHILE;
    CLOSE cur;
END

3. 生成 ID 路徑

CREATE PROCEDURE pro_cre_pathlist(IN nid INT, IN delimit VARCHAR(10), INOUT pathstr VARCHAR(1000))
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE parentid INT;
    DECLARE cur CURSOR FOR 
        SELECT t.parent_id, CONCAT(CAST(t.parent_id AS CHAR), delimit, pathstr)
        FROM channel t WHERE t.id = nid;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    SET max_sp_recursion_depth = 12;
    OPEN cur;
    FETCH cur INTO parentid, pathstr;
    WHILE done = 0 DO
        CALL pro_cre_pathlist(parentid, delimit, pathstr);
        FETCH cur INTO parentid, pathstr;
    END WHILE;
    CLOSE cur;
END

4. 生成名稱路徑

CREATE PROCEDURE pro_cre_pnlist(IN nid INT, IN delimit VARCHAR(10), INOUT pathstr VARCHAR(1000))
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE parentid INT;
    DECLARE cur CURSOR FOR 
        SELECT t.parent_id, CONCAT(t.cname, delimit, pathstr)
        FROM channel t WHERE t.id = nid;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    SET max_sp_recursion_depth = 12;
    OPEN cur;
    FETCH cur INTO parentid, pathstr;
    WHILE done = 0 DO
        CALL pro_cre_pnlist(parentid, delimit, pathstr);
        FETCH cur INTO parentid, pathstr;
    END WHILE;
    CLOSE cur;
END

封裝函數與調用過程

• fn_tree_path(nid, delimit)：返回 ID 路徑
• fn_tree_pathname(nid, delimit)：返回名稱路徑
• pro_show_childLst(rootId)：顯示子節點樹
• pro_show_parentLst(rootId)：顯示父節點鏈

測試結果示例

CALL pro_show_childLst(-1);
+----+-----------------------+-----------+-------+-------------+----------------------------+
| id | NAME                  | parent_id | depth | path        | pathname                   |
+----+-----------------------+-----------+-------+-------------+----------------------------+
| 13 | --首頁                | -1        | 1     | -1/13       | 首頁/                      |
| 16 | ----左上幻燈片        | 13        | 2     | -1/13/16    | 首頁/左上幻燈片/           |
| 14 | --TV580               | -1        | 1     | -1/14       | TV580/                     |
| 17 | ----幫忙              | 14        | 2     | -1/14/17    | TV580/幫忙/                |
| 18 | ------欄目簡介        | 17        | 3     | -1/14/17/18 | TV580/幫忙/欄目簡介/       |
| 15 | --生活580             | -1        | 1     | -1/15       | 生活580/                   |
+----+-----------------------+-----------+-------+-------------+----------------------------+

適用場景： 多級選單渲染、組織架構查詢、分類目錄遍歷。注意遞歸深度受 max_sp_recursion_depth 限制，大數據量建議改用遞歸 CTE (MySQL 8.0+) 或應用層處理。