您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 存儲過程實現樹形結構遞歸遍歷：子孫/祖先查詢與路徑生成完整方案

編輯：MySQL 資料庫相關知識

MySQL 無原生 CONNECT BY (Oracle) 或 WITH RECURSIVE (8.0+ CTE) 支援時，可透過**存儲過程 + 臨時表 + 游標遞歸** 實現樹形遍歷。本文提供可直接部署的完整方案：建表、測試數據、六個核心存儲過程/函數、調用示例與結果驗證。

1. 測試表與數據

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

INSERT INTO `channel` (`id`,`cname`,`parent_id`) VALUES
(13,'首頁',-1),
(14,'TV580',-1),
(15,'生活580',-1),
(16,'左上幻燈片',13),
(17,'幫忙',14),
(18,'欄目簡介',17);

2. 核心存儲過程/函數定義

2.1 向下遞歸：查詢指定節點所有子孫

DROP PROCEDURE IF EXISTS `pro_cre_childlist`;
CREATE PROCEDURE `pro_cre_childlist`(IN rootId INT, IN nDepth INT)
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE b INT;
  DECLARE cur1 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 cur1;
  FETCH cur1 INTO b;
  WHILE done = 0 DO
    CALL `pro_cre_childlist`(b, nDepth + 1);
    FETCH cur1 INTO b;
  END WHILE;
  CLOSE cur1;
END;

2.2 向上遞歸：查詢指定節點所有祖先

DROP PROCEDURE IF EXISTS `pro_cre_parentlist`;
CREATE PROCEDURE `pro_cre_parentlist`(IN rootId INT, IN nDepth INT)
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE b INT;
  DECLARE cur1 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 cur1;
  FETCH cur1 INTO b;
  WHILE done = 0 DO
    CALL `pro_cre_parentlist`(b, nDepth + 1);
    FETCH cur1 INTO b;
  END WHILE;
  CLOSE cur1;
END;

2.3 遞歸生成 ID 路徑 (模擬 Oracle SYS_CONNECT_BY_PATH)

DROP PROCEDURE IF EXISTS `pro_cre_pathlist`;
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 DEFAULT 0;
  DECLARE cur1 CURSOR FOR
    SELECT `parent_id`, CONCAT(CAST(`parent_id` AS CHAR), delimit, pathstr)
    FROM `channel` WHERE `id` = nid;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  SET @@max_sp_recursion_depth = 12;
  OPEN cur1;
  FETCH cur1 INTO parentid, pathstr;
  WHILE done = 0 DO
    CALL `pro_cre_pathlist`(parentid, delimit, pathstr);
    FETCH cur1 INTO parentid, pathstr;
  END WHILE;
  CLOSE cur1;
END;

2.4 遞歸生成名稱路徑

DROP PROCEDURE IF EXISTS `pro_cre_pnlist`;
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 DEFAULT 0;
  DECLARE cur1 CURSOR FOR
    SELECT `parent_id`, CONCAT(`cname`, delimit, pathstr)
    FROM `channel` WHERE `id` = nid;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  SET @@max_sp_recursion_depth = 12;
  OPEN cur1;
  FETCH cur1 INTO parentid, pathstr;
  WHILE done = 0 DO
    CALL `pro_cre_pnlist`(parentid, delimit, pathstr);
    FETCH cur1 INTO parentid, pathstr;
  END WHILE;
  CLOSE cur1;
END;

2.5 封裝函數：獲取 ID 路徑

DROP FUNCTION IF EXISTS `fn_tree_path`;
CREATE FUNCTION `fn_tree_path`(nid INT, delimit VARCHAR(10)) RETURNS VARCHAR(2000) CHARSET utf8
BEGIN
  DECLARE pathid VARCHAR(1000);
  SET pathid = CAST(nid AS CHAR);
  CALL `pro_cre_pathlist`(nid, delimit, pathid);
  RETURN pathid;
END;

2.6 封裝函數：獲取名稱路徑

DROP FUNCTION IF EXISTS `fn_tree_pathname`;
CREATE FUNCTION `fn_tree_pathname`(nid INT, delimit VARCHAR(10)) RETURNS VARCHAR(2000) CHARSET utf8
BEGIN
  DECLARE pathid VARCHAR(1000);
  SET pathid = '';
  CALL `pro_cre_pnlist`(nid, delimit, pathid);
  RETURN pathid;
END;

2.7 顯示子孫節點 (含層級、路徑)

DROP PROCEDURE IF EXISTS `pro_show_childLst`;
CREATE PROCEDURE `pro_show_childLst`(IN rootId INT)
BEGIN
  DROP TEMPORARY TABLE IF EXISTS `tmpLst`;
  CREATE TEMPORARY TABLE `tmpLst` (
    `sno` INT PRIMARY KEY AUTO_INCREMENT,
    `id` INT, `depth` INT
  );

  CALL `pro_cre_childlist`(rootId, 0);

  SELECT c.`id`,
         CONCAT(SPACE(t.`depth`*2), '--', c.`cname`) AS `NAME`,
         c.`parent_id`, t.`depth`,
         `fn_tree_path`(c.`id`, '/') AS `path`,
         `fn_tree_pathname`(c.`id`, '/') AS `pathname`
  FROM `tmpLst` t JOIN `channel` c ON t.`id` = c.`id`
  ORDER BY t.`sno`;
END;

2.8 顯示祖先節點

DROP PROCEDURE IF EXISTS `pro_show_parentLst`;
CREATE PROCEDURE `pro_show_parentLst`(IN rootId INT)
BEGIN
  DROP TEMPORARY TABLE IF EXISTS `tmpLst`;
  CREATE TEMPORARY TABLE `tmpLst` (
    `sno` INT PRIMARY KEY AUTO_INCREMENT,
    `id` INT, `depth` INT
  );

  CALL `pro_cre_parentlist`(rootId, 0);

  SELECT c.`id`,
         CONCAT(SPACE(t.`depth`*2), '--', c.`cname`) AS `NAME`,
         c.`parent_id`, t.`depth`,
         `fn_tree_path`(c.`id`, '/') AS `path`,
         `fn_tree_pathname`(c.`id`, '/') AS `pathname`
  FROM `tmpLst` t JOIN `channel` c ON t.`id` = c.`id`
  ORDER BY t.`sno`;
END;

3. 調用驗證與結果展示

3.1 從根節點 -1 顯示所有子孫

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/                      |
+----+---------------------------+-----------+-------+-------------------+-------------------------------+

3.2 指定節點查詢子孫 (如 TV580 id=14)

CALL `pro_show_childLst`(14);

+----+------------------+-----------+-------+-----------------+--------------------------+
| id | NAME             | parent_id | depth | path            | pathname                 |
+----+------------------+-----------+-------+-----------------+--------------------------+
| 14 | --TV580          | -1        | 0     | -1/14           | TV580/                   |
| 17 | ----幫忙         | 14        | 1     | -1/14/17        | TV580/幫忙/              |
| 18 | ------欄目簡介   | 17        | 2     | -1/14/17/18     | TV580/幫忙/欄目簡介/     |
+----+------------------+-----------+-------+-----------------+--------------------------+

3.3 查詢葉子節點祖先 (欄目簡介 id=18)

CALL `pro_show_parentLst`(18);

+----+-------------------+-----------+-------+-------------------------+----------------------------------+
| id | NAME              | parent_id | depth | path                    | pathname                         |
+----+-------------------+-----------+-------+-------------------------+----------------------------------+
| 18 | --欄目簡介        | 17        | 0     | -1/14/17/18             | TV580/幫忙/欄目簡介/             |
| 17 | ----幫忙          | 14        | 1     | -1/14/17                | TV580/幫忙/                      |
| 14 | ------TV580       | -1        | 2     | -1/14                   | TV580/                           |
+----+-------------------+-----------+-------+-------------------------+----------------------------------+

4. 方案優缺點與現代替代方案

維度	存儲過程遞歸方案	MySQL 8.0+ CTE (WITH RECURSIVE)
相容版本	5.0+	8.0+
效能	游標逐行、臨時表開銷大	優化器原生支援、集合運算、效能優
維護性	多對象、調試困難	單條 SQL、可讀性強
遞歸深度限制	`max_sp_recursion_depth` (預設 0 需調大)	`cte_max_recursion_depth` (預設 1000)

5. MySQL 8.0+ CTE 寫法參考 (推薦新專案使用)

-- 向下查詢所有子孫
WITH RECURSIVE cte AS (
  SELECT id, cname, parent_id, 0 AS depth, CAST(id AS CHAR) AS path
  FROM channel WHERE id = 14
  UNION ALL
  SELECT c.id, c.cname, c.parent_id, cte.depth + 1, CONCAT(cte.path, '/', c.id)
  FROM channel c JOIN cte ON c.parent_id = cte.id
)
SELECT * FROM cte;

-- 向上查詢所有祖先
WITH RECURSIVE cte AS (
  SELECT id, cname, parent_id, 0 AS depth, CAST(id AS CHAR) AS path
  FROM channel WHERE id = 18
  UNION ALL
  SELECT c.id, c.cname, c.parent_id, cte.depth + 1, CONCAT(c.id, '/', cte.path)
  FROM channel c JOIN cte ON c.id = cte.parent_id
)
SELECT * FROM cte;