萬盛學電腦網

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

MySQL 儲存過程實現 Oracle CONNECT BY 鄰接模型樹狀查詢

背景:MySQL 缺乏原生遞迴查詢

Oracle 提供 CONNECT BY PRIOR 輕鬆處理鄰接模型(Adjacency List)的層級資料。MySQL 雖無此語法,但支援儲存過程,可透過程式化邏輯模擬遞迴遍歷,實現樹深度、葉節點、根節點與完整路徑查詢。

測試資料結構

CREATE TABLE country (
  id INT NOT NULL,
  name VARCHAR(60) NOT NULL
);

CREATE TABLE country_relation (
  id INT,
  parentid INT
);

插入 16 筆國家/地區層級資料(Earth → 洲 → 國家 → 州/省)。

Oracle 原生寫法回顧

  • 樹深度: SELECT MAX(LEVEL) FROM ... CONNECT BY PRIOR id = parentid
  • 葉節點: 使用 CONNECT_BY_ISLEAF 偽欄位。
  • 根節點: CONNECT_BY_ROOT name。
  • 完整路徑: SYS_CONNECT_BY_PATH(name, '/')。

MySQL 簡易查詢(前三項)

1. 查詢樹深度

SELECT COUNT(DISTINCT IFNULL(parentid, -1)) AS LEVEL
FROM country_relation; -- 結果:4

2. 查詢根節點

SELECT b.name AS root_node
FROM (SELECT id FROM country_relation WHERE parentid IS NULL) a
JOIN country b ON a.id = b.id; -- 結果:Earth

3. 查詢葉節點

SELECT b.name AS leaf_node
FROM (
  SELECT id FROM country_relation
  WHERE id NOT IN (SELECT IFNULL(parentid, -1) FROM country_relation)
) a
JOIN country b ON a.id = b.id; -- 返回 13 筆葉節點

核心難點:完整路徑遍歷 → 儲存過程實現

MySQL 無單一 SQL 完成路徑遞迴,需用儲存過程 + 暫存表 + 游標迴圈。

儲存過程核心邏輯

  1. 建立暫存表 tmp_country_list(node_level, node_path)。
  2. 插入根節點(parentid IS NULL),層級=1,路徑=/id。
  3. 游標遍歷所有非空 parentid(分組排序),逐層向下:
    • 讀取上一層對應父節點的 node_path。
    • 拼接當前節點 ID,寫入暫存表。
  4. 第二階段:將暫存表中所有 /id 替換為實際 /name。
  5. 最終 SELECT node_path FROM tmp_country_list 輸出完整路徑。

完整儲存過程碼(精簡版)

DELIMITER $$
CREATE PROCEDURE `sp_show_list`()
BEGIN
  DROP TEMPORARY TABLE IF EXISTS tmp_country_list;
  CREATE TEMPORARY TABLE tmp_country_list (
    node_level INT UNSIGNED NOT NULL,
    node_path VARCHAR(1000) NOT NULL
  );

  -- 根節點
  INSERT INTO tmp_country_list
  SELECT 1, CONCAT('/', id) FROM country_relation WHERE parentid IS NULL;

  -- 逐層遞迴
  BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE v_parentid INT;
    DECLARE v_level INT DEFAULT 1;
    DECLARE cr CURSOR FOR
      SELECT parentid FROM country_relation
      WHERE parentid IS NOT NULL GROUP BY parentid ORDER BY parentid;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cr;
    read_loop: LOOP
      FETCH cr INTO v_parentid;
      IF done THEN LEAVE read_loop; END IF;
      SET v_level = v_level + 1;

      -- 取得父路徑
      DECLARE v_path VARCHAR(1000) DEFAULT '';
      SELECT node_path INTO v_path
      FROM tmp_country_list
      WHERE node_level = v_level - 1 AND LOCATE(v_parentid, node_path) > 0
      LIMIT 1;

      INSERT INTO tmp_country_list
      SELECT v_level, CONCAT(IFNULL(v_path, ''), '/', id)
      FROM country_relation WHERE parentid = v_parentid;
    END LOOP;
    CLOSE cr;
  END;

  -- ID 轉名稱
  BEGIN
    DECLARE max_lvl INT;
    DECLARE i INT DEFAULT 0;
    SELECT MAX(node_level) INTO max_lvl FROM tmp_country_list;
    WHILE i < max_lvl DO
      UPDATE tmp_country_list a
      JOIN country b ON LOCATE(CONCAT('/', b.id), a.node_path) > 0
      SET a.node_path = REPLACE(a.node_path, CONCAT('/', b.id), CONCAT('/', b.name));
      SET i = i + 1;
    END WHILE;
  END;

  SELECT node_path FROM tmp_country_list;
END$$
DELIMITER ;

呼叫結果

CALL sp_show_list();
/Earth
/Earth/North America
/Earth/North America/United States
/Earth/North America/United States/California
...(共 16 行)

總結

雖然 MySQL 8.0+ 已支援 WITH RECURSIVE(CTE),但在舊版本或需複雜程式控制時,儲存過程方案仍具參考價值。關鍵點在於:暫存表記錄層級與路徑、游標逐層展開、最後批次替換 ID 為名稱。

copyright © 萬盛學電腦網 all rights reserved