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 → 洲 → 國家 → 州/省)。
SELECT MAX(LEVEL) FROM ... CONNECT BY PRIOR id = parentidCONNECT_BY_ISLEAF 偽欄位。CONNECT_BY_ROOT name。SYS_CONNECT_BY_PATH(name, '/')。SELECT COUNT(DISTINCT IFNULL(parentid, -1)) AS LEVEL
FROM country_relation; -- 結果:4
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
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 完成路徑遞迴,需用儲存過程 + 暫存表 + 游標迴圈。
tmp_country_list(node_level, node_path)。parentid IS NULL),層級=1,路徑=/id。parentid(分組排序),逐層向下:
node_path。/id 替換為實際 /name。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 為名稱。