萬盛學電腦網

瀏覽器 Windows 10 WPS Office 防毒軟體 資料庫 Excel 教學 檔案管理 Word 教學 網頁製作 裝機必備軟體 Linux 教學

您现在的位置：萬盛學電腦網 >> 程式設計 > >> 資料庫 >> MySQL 教學

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

前言

MySQL 8.0 前無原生 CONNECT BY 或 WITH RECURSIVE（8.0 已支援 CTE），處理多級菜單、組織架構、分類樹等層級數據常需自研遞歸方案。本文基於存儲過程+臨時表實現：向下遍歷子孫、向上追溯祖先、生成 ID/名稱路徑，兼容 5.6/5.7，附完整測試用例。

一、測試數據準備

DROP TABLE IF EXISTS `channel`;
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=utf8mb4;

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

樹形結構：

-1 (虛擬根)
├─ 13 首頁
│  └─ 16 左上幻燈片
├─ 14 TV580
│  └─ 17 幫忙
│     └─ 18 欄目簡介
└─ 15 生活580

二、核心存儲過程定義

1. 臨時表結構

CREATE TEMPORARY TABLE IF NOT EXISTS `tmpLst` (
  `sno` INT PRIMARY KEY AUTO_INCREMENT,
  `id` INT,
  `depth` INT
) ENGINE=Memory;

2. 向下遞歸：獲取指定節點所有子孫

DROP PROCEDURE IF EXISTS `pro_cre_childlist`;
DELIMITER //
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 = 255;  -- 允許深度遞歸

  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//
DELIMITER ;

3. 向上遞歸：獲取指定節點所有祖先

DROP PROCEDURE IF EXISTS `pro_cre_parentlist`;
DELIMITER //
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 = 255;

  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//
DELIMITER ;

4. 生成 ID 路徑（類似 Oracle SYS_CONNECT_BY_PATH）

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

  SET @@max_sp_recursion_depth = 255;

  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//
DELIMITER ;

5. 生成名稱路徑

DROP PROCEDURE IF EXISTS `pro_cre_pnlist`;
DELIMITER //
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 `t`.`parent_id`, CONCAT(`t`.`cname`, `delimit`, `pathstr`)
    FROM `channel` AS `t` WHERE `t`.`id` = `nid`;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET `done` = 1;

  SET @@max_sp_recursion_depth = 255;

  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//
DELIMITER ;

6. 封裝函數：對外調用

-- ID 路徑函數
DROP FUNCTION IF EXISTS `fn_tree_path`;
DELIMITER //
CREATE FUNCTION `fn_tree_path`(`nid` INT, `delimit` VARCHAR(10)) RETURNS VARCHAR(2000) CHARSET utf8mb4
BEGIN
  DECLARE `pathid` VARCHAR(1000);
  SET `pathid` = CAST(`nid` AS CHAR);
  CALL `pro_cre_pathlist`(`nid`, `delimit`, `pathid`);
  RETURN `pathid`;
END//
DELIMITER ;

-- 名稱路徑函數
DROP FUNCTION IF EXISTS `fn_tree_pathname`;
DELIMITER //
CREATE FUNCTION `fn_tree_pathname`(`nid` INT, `delimit` VARCHAR(10)) RETURNS VARCHAR(2000) CHARSET utf8mb4
BEGIN
  DECLARE `pathid` VARCHAR(1000) DEFAULT '';
  CALL `pro_cre_pnlist`(`nid`, `delimit`, `pathid`);
  RETURN `pathid`;
END//
DELIMITER ;

7. 封裝展示過程

-- 顯示子孫節點
DROP PROCEDURE IF EXISTS `pro_show_childLst`;
DELIMITER //
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
  ) ENGINE=Memory;

  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//
DELIMITER ;

-- 顯示祖先節點
DROP PROCEDURE IF EXISTS `pro_show_parentLst`;
DELIMITER //
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
  ) ENGINE=Memory;

  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//
DELIMITER ;

三、測試驗證

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

2. 查詢指定節點子孫（如 TV580）

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. 向上追溯祖先（如欄目簡介）

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

四、MySQL 8.0+ 現代寫法（CTE 遞歸）

-- 向下遍歷子孫
WITH RECURSIVE `cte` AS (
  SELECT `id`, `cname`, `parent_id`, 0 AS `depth`, CAST(`id` AS CHAR(1000)) 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(1000)) 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`;

五、性能與替代方案對比

方案	適用版本	寫入性能	讀取性能	維護成本	備註
存儲過程+臨時表	5.6/5.7	高	中（遞歸開銷）	高	需設置 max_sp_recursion_depth
CTE 遞歸	8.0+	高	高（優化器支援）	低	標準 SQL，可直接 JOIN
Closure Table (閉包表)	所有	中（需維護關係表）	極高	中	適合頻繁讀取、層級深
Path Enumeration (物化路徑)	所有	中	高	低	字段存路徑如 1/3/5/，LIKE 查詢
Nested Set (嵌套集合)	所有	低（移動節點需重算左右值）	高	高	適合極少寫入、大量讀取

六、總結

MySQL 5.6/5.7 時代，存儲過程+臨時表是實現樹形遞歸的主流方案；升級至 8.0 後，WITH RECURSIVE CTE 成為標準、高效、可維護的首選。根據業務讀寫比例、樹深度、遷移成本，可選擇 Closure Table 或 Path Enumeration 作為物化設計方案。

上一篇程序編程： CentOS 7 安裝 MySQL 5.6 完整教程：替換 MariaDB、配置 UTF-8、開放遠程訪問
下一篇程序編程： MySQL 主主雙向同步部署指南：雙主架構配置、自增偏移避衝突、故障切換實戰

萬盛學電腦網

您现在的位置：萬盛學電腦網 >> 程式設計 > >> 資料庫 >> MySQL 教學

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

前言

一、測試數據準備

二、核心存儲過程定義

1. 臨時表結構

2. 向下遞歸：獲取指定節點所有子孫

3. 向上遞歸：獲取指定節點所有祖先

4. 生成 ID 路徑（類似 Oracle SYS_CONNECT_BY_PATH）

5. 生成名稱路徑

6. 封裝函數：對外調用

7. 封裝展示過程

三、測試驗證

1. 從根節點查所有子孫

2. 查詢指定節點子孫（如 TV580）

3. 向上追溯祖先（如欄目簡介）

四、MySQL 8.0+ 現代寫法（CTE 遞歸）

五、性能與替代方案對比

六、總結

MySQL 教學排行

推薦文章

萬盛學電腦網| 日本語版 | 設為首頁 | 加入收藏

萬盛學電腦網

您现在的位置： 萬盛學電腦網 >> 程式設計 > >> 資料庫 >> MySQL 教學

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

前言

一、測試數據準備

二、核心存儲過程定義

1. 臨時表結構

2. 向下遞歸：獲取指定節點所有子孫

3. 向上遞歸：獲取指定節點所有祖先

4. 生成 ID 路徑（類似 Oracle SYS_CONNECT_BY_PATH）

5. 生成名稱路徑

6. 封裝函數：對外調用

7. 封裝展示過程

三、測試驗證

1. 從根節點查所有子孫

2. 查詢指定節點子孫（如 TV580）

3. 向上追溯祖先（如欄目簡介）

四、MySQL 8.0+ 現代寫法（CTE 遞歸）

五、性能與替代方案對比

六、總結

MySQL 教學排行

推薦文章

萬盛學電腦網| 日本語版 | 設為首頁 | 加入收藏

您现在的位置：萬盛學電腦網 >> 程式設計 > >> 資料庫 >> MySQL 教學