MySQL預存程序實現Oracle鄰接模型樹形處理的方法執行個體

資料庫對階層的處理模型有好多種，可以根據自己的需求來設計模型，當然最簡單的也是最容易設計的模型就是所謂的鄰接模型。在這方面，其他資料庫比如Oracle 提供了現成的分析方法 connect by，而MySQL在這方面就顯得有些薄弱了。 不過可以用MySQL的預存程序實現ORACLE類似的分析功能


這樣，先來建立一個簡單的數表。

代碼如下	複製代碼
create table country ( id number(2) not null, name varchar(60) not null); create table country_relation (id number(2), parentid number(2));

插入一些資料

代碼如下

複製代碼

-- Table country. insert into country (id,name) values (0,'Earth'); insert into country (id,name) values (2,'North America'); insert into country (id,name) values (3,'South America'); insert into country (id,name) values (4,'Europe'); insert into country (id,name) values (5,'Asia'); insert into country (id,name) values (6,'Africa'); insert into country (id,name) values (7,'Australia'); insert into country (id,name) values (8,'Canada'); insert into country (id,name) values (9,'Central America'); insert into country (id,name) values (10,'Island Nations'); insert into country (id,name) values (11,'United States'); insert into country (id,name) values (12,'Alabama'); insert into country (id,name) values (13,'Alaska'); insert into country (id,name) values (14,'Arizona'); insert into country (id,name) values (15,'Arkansas'); insert into country (id,name) values (16,'California'); -- Table country_relation. insert into country_relation (id,parentid) values (0,NULL); insert into country_relation (id,parentid) values (2,0); insert into country_relation (id,parentid) values (3,0); insert into country_relation (id,parentid) values (4,0); insert into country_relation (id,parentid) values (5,0); insert into country_relation (id,parentid) values (6,0); insert into country_relation (id,parentid) values (7,0); insert into country_relation (id,parentid) values (8,2); insert into country_relation (id,parentid) values (9,2); insert into country_relation (id,parentid) values (10,2); insert into country_relation (id,parentid) values (11,2); insert into country_relation (id,parentid) values (12,11); insert into country_relation (id,parentid) values (13,11); insert into country_relation (id,parentid) values (14,11); insert into country_relation (id,parentid) values (15,11); insert into country_relation (id,parentid) values (16,11);

在Oracle 裡面，對這些操作就比較簡單了，都是系統提供的。
比如下面四種情形：
1）. 查看深度，

代碼如下	複製代碼
select max(level) "level" from COUNTRY_RELATION a start with a.parentid is NULL connect by PRIOR a.id = a.PARENTID order by level;      level ----------          4 經過時間: 00: 00: 00.03

2）. 查看葉子節點

代碼如下

複製代碼

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; NAME -------------------------------------------------- Canada Central America Island Nations Alabama Alaska Arizona Arkansas California South America Europe Asia Africa Australia 已選擇13行。 經過時間: 00: 00: 00.01

3） 查看ROOT節點

代碼如下	複製代碼
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 CONNECT_BY_ROOTB.NAME -------------------------------------------------- Earth 經過時間: 00: 00: 00.01

4）. 查看路徑

代碼如下

複製代碼

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; path -------------------------------------------------- /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 已選擇16行。 經過時間: 00: 00: 00.01

接下來我們看看在MySQL 裡面如何?上面四種情形：
前三種都比較簡單，可以很容易寫出SQL。
1）查看深度

代碼如下	複製代碼
mysql> SELECT COUNT(DISTINCT IFNULL(parentid,-1)) AS LEVEL FROM country_relation ; +-------+ | LEVEL | +-------+ | 4 | +-------+ 1 row in set (0.00 sec)

2）查看ROOT節點

代碼如下	複製代碼
mysql> SELECT b.`name` AS root_node FROM     -> (     -> SELECT id FROM country_relation WHERE parentid IS NULL     -> ) AS a, country AS b WHERE a.id = b.id; +-----------+ | root_node | +-----------+ | Earth | +-----------+ 1 row in set (0.00 sec)

3).  查看葉子節點

代碼如下

複製代碼

mysql> 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, country AS b WHERE a.id = b.id; +-----------------+ | leaf_node | +-----------------+ | South America | | Europe | | Asia | | Africa | | Australia | | Canada | | Central America | | Island Nations | | Alabama | | Alaska | | Arizona | | Arkansas | | California | +-----------------+ 13 rows in set (0.00 sec) mysql>

4） 查看路徑
這一塊沒有簡單的SQL實現，不過可以用MySQL的預存程序來實現同樣的功能。
預存程序代碼如下：

代碼如下

複製代碼

DELIMITER $$ USE `t_girl`$$ DROP PROCEDURE IF EXISTS `sp_show_list`$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_show_list`() BEGIN       -- Created by ytt 2014/11/04.       -- Is equal to oracle's connect by syntax.       -- Body.       DROP TABLE IF EXISTS tmp_country_list;       CREATE TEMPORARY TABLE tmp_country_list (node_level INT UNSIGNED NOT NULL, node_path VARCHAR(1000) NOT NULL);       -- Get the root node.       INSERT INTO tmp_country_list SELECT 1, CONCAT('/',id) FROM country_relation WHERE parentid IS NULL;       -- Loop within all parent node.       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;             -- Get the upper path.             SELECT node_path FROM tmp_country_list WHERE node_level = i1 - 1 AND LOCATE(v_parentid,node_path) > 0 INTO v_node_path;             -- Escape the outer not found exception.             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;       -- Update node's id to its real name.       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 ;

調用結果：

代碼如下

複製代碼

mysql> CALL sp_show_list(); +-----------------------------------------------+ | node_path | +-----------------------------------------------+ | /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 | +-----------------------------------------------+ 16 rows in set (0.04 sec) Query OK, 0 rows affected (0.08 sec) mysql>