您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 教學

MySQL 模擬 Oracle CONNECT BY 層次查詢實戰

編輯：Oracle 教學

MySQL 模擬 Oracle CONNECT BY 層次查詢實戰

資料庫處理階層結構資料有多種模型，最直觀的是鄰接模型。Oracle 內建 CONNECT BY 支援遞迴查詢，MySQL 則較薄弱。本文展示如何在 MySQL 中透過儲存程序實現 Oracle 風格的層次查詢功能（查看深度、根節點、葉節點、完整路徑）。

1. 測試資料準備

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

CREATE TABLE country_relation (
    id INT(2),
    parentid INT(2)
);

-- 插入測試資料（地理層級：地球->洲->國->州/省）
INSERT INTO country VALUES 
(0,'Earth'), (2,'North America'), (3,'South America'), (4,'Europe'),
(5,'Asia'), (6,'Africa'), (7,'Australia'), (8,'Canada'), (9,'Central America'),
(10,'Island Nations'), (11,'United States'), (12,'Alabama'), (13,'Alaska'),
(14,'Arizona'), (15,'Arkansas'), (16,'California');

INSERT INTO country_relation VALUES
(0,NULL), (2,0), (3,0), (4,0), (5,0), (6,0), (7,0),
(8,2), (9,2), (10,2), (11,2),
(12,11), (13,11), (14,11), (15,11), (16,11);

2. Oracle 原生 CONNECT BY 查詢

查看樹深度

SELECT MAX(level) "level" 
FROM country_relation a 
START WITH a.parentid IS NULL 
CONNECT BY PRIOR a.id = a.parentid 
ORDER BY level;
-- 結果：4

查看葉節點

SELECT name FROM (
    SELECT b.name, CONNECT_BY_ISLEAF "isleaf"
    FROM country_relation a 
    INNER JOIN country b ON (a.id = b.id)
    START WITH a.parentid IS NULL 
    CONNECT BY PRIOR a.id = a.parentid
) T WHERE T."isleaf" = 1;
-- 結果：Canada, Central America, Island Nations, Alabama, Alaska, Arizona, Arkansas, California, South America, Europe, Asia, Africa, Australia

查看根節點

SELECT CONNECT_BY_ROOT b.name 
FROM country_relation a 
INNER JOIN country b ON (a.id = b.id)
START WITH a.parentid IS NULL 
CONNECT BY a.id = a.parentid;
-- 結果：Earth

查看完整路徑

SELECT SYS_CONNECT_BY_PATH(b.name,'/') "path"
FROM country_relation a 
INNER JOIN country b ON (a.id = b.id)
START WITH a.parentid IS NULL 
CONNECT BY PRIOR a.id = a.parentid
ORDER BY level, a.id;
-- 結果：/Earth, /Earth/North America, ..., /Earth/North America/United States/California

3. MySQL 等效實現

查看深度

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) AS 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)
) AS a
JOIN country b ON a.id = b.id;
-- 結果：13 筆葉節點記錄

查看完整路徑（儲存程序實現）

DELIMITER $$
USE `t_girl`$$
DROP PROCEDURE IF EXISTS `sp_show_list`$$
CREATE DEFINER=`root`@`localhost` 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;
    
    -- 迴圈處理所有父節點
    cursor1:BEGIN
        DECLARE done1 INT DEFAULT 0;
        DECLARE i1 INT DEFAULT 1;
        DECLARE v_parentid INT DEFAULT -1;
        DECLARE v_node_path VARCHAR(1000) DEFAULT '';
        DECLARE cr1 CURSOR FOR 
            SELECT parentid FROM country_relation 
            WHERE parentid IS NOT NULL 
            GROUP BY parentid ORDER BY parentid ASC;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = 1;
        
        OPEN cr1;
        loop1:LOOP
            FETCH cr1 INTO v_parentid;
            IF done1 = 1 THEN LEAVE loop1; END IF;
            SET i1 = i1 + 1;
            
            label_path:BEGIN
                DECLARE done2 INT DEFAULT 0;
                DECLARE CONTINUE HANDLER FOR NOT FOUND SET done2 = 1;
                -- 取得上層路徑
                SELECT node_path FROM tmp_country_list 
                WHERE node_level = i1 - 1 AND LOCATE(v_parentid, node_path) > 0
                INTO v_node_path;
                IF done2 = 1 THEN SET done2 = 0; END IF;
                
                INSERT INTO tmp_country_list 
                SELECT i1, CONCAT(IFNULL(v_node_path,''),'/',id) 
                FROM country_relation WHERE parentid = v_parentid;
            END;
        END LOOP;
        CLOSE cr1;
    END;
    
    -- 將節點 ID 替換為真實名稱
    update_name_label:BEGIN
        DECLARE cnt INT DEFAULT 0;
        DECLARE i2 INT DEFAULT 0;
        SELECT MAX(node_level) FROM tmp_country_list INTO cnt;
        WHILE i2 < cnt DO
            UPDATE tmp_country_list AS a, country AS b
            SET a.node_path = REPLACE(a.node_path, CONCAT('/',b.id), CONCAT('/',b.name))
            WHERE LOCATE(CONCAT('/',b.id), a.node_path) > 0;
            SET i2 = i2 + 1;
        END WHILE;
    END;
    
    SELECT node_path FROM tmp_country_list;
END$$
DELIMITER ;

呼叫結果

CALL sp_show_list();
/Earth
/Earth/North America
/Earth/South America
/Earth/Europe
/Earth/Asia
/Earth/Africa
/Earth/Australia
/Earth/North America/Canada
/Earth/North America/Central America
/Earth/North America/Island Nations
/Earth/North America/United States
/Earth/North America/United States/Alabama
/Earth/North America/United States/Alaska
/Earth/North America/United States/Arizona
/Earth/North America/United States/Arkansas
/Earth/North America/United States/California