MySQL存储过程实现Oracle邻接模型树形处理的方法实例

项目需求用到了邻接模型,但是是采用开源的Mysql,而Mysql没有这个功能,Oracle数据库提供了现在的分析方法 connect by 处理邻接模型,不过mysql支持存储过程,可以建立存储过程实现Oracle的分析功能.

数据库对层次结构的处理模型有好多种,可以根据自己的需求来设计模型,当然最简单的也是最容易设计的模型就是所谓的邻接模型,在这方面,其他数据库比如Oracle 提供了现成的分析方法 connect by,而MySQL在这方面就显得有些薄弱了,不过可以用MySQL的存储过程实现ORACLE类似的分析功能.

这样,先来创建一个简单的数表,代码如下:

1. create table country ( id number(2) not null, name varchar(60) not null); 
2.  
3. create table country_relation (id number(2), parentid number(2)); 
4.  
5. create table country_relation (id number(2), parentid number(2)); 

插入一些数据,代码如下:

1. -- Table country. 
2. insert into country (id,name) values (0,'Earth'); 
3. insert into country (id,name) values (2,'North America'); 
4. insert into country (id,name) values (3,'South America'); 
5. insert into country (id,name) values (4,'Europe'); 
6. insert into country (id,name) values (5,'Asia'); 
7. insert into country (id,name) values (6,'Africa'); 
8. insert into country (id,name) values (7,'Australia'); 
9. insert into country (id,name) values (8,'Canada'); 
10. insert into country (id,name) values (9,'Central America'); 
11. insert into country (id,name) values (10,'Island Nations'); 
12. insert into country (id,name) values (11,'United States'); 
13. insert into country (id,name) values (12,'Alabama'); 
14. insert into country (id,name) values (13,'Alaska'); 
15. insert into country (id,name) values (14,'Arizona'); 
16. insert into country (id,name) values (15,'Arkansas'); 
17. insert into country (id,name) values (16,'California'); 
18.  
19.  
20. -- Table country_relation. 
21. insert into country_relation (id,parentid) values (0,NULL); 
22. insert into country_relation (id,parentid) values (2,0); 
23. insert into country_relation (id,parentid) values (3,0); 
24. insert into country_relation (id,parentid) values (4,0); 
25. insert into country_relation (id,parentid) values (5,0); 
26. insert into country_relation (id,parentid) values (6,0); 
27. insert into country_relation (id,parentid) values (7,0); 
28. insert into country_relation (id,parentid) values (8,2); 
29. insert into country_relation (id,parentid) values (9,2); 
30. insert into country_relation (id,parentid) values (10,2); 
31. insert into country_relation (id,parentid) values (11,2); 
32. insert into country_relation (id,parentid) values (12,11); 
33. insert into country_relation (id,parentid) values (13,11); 
34. insert into country_relation (id,parentid) values (14,11); 
35. insert into country_relation (id,parentid) values (15,11); 
36. insert into country_relation (id,parentid) values (16,11); 

在Oracle 里面,对这些操作就比较简单了,都是系统提供的,比如下面四种情形.

1).查看深度,代码如下:

1. select max(level) "level" from COUNTRY_RELATION a start with a.parentid is NULL 
2. connect by PRIOR a.id = a.PARENTID 
3. order by level; 
4.  
5.      level 
6. ---------- 
7.          4 
8.  
9. --已用时间: 00: 00: 00.03 

2).查看叶子节点,代码如下:

1. select name from  
2. ( 
3. select b.name, connect_by_isleaf "isleaf" 
4. from COUNTRY_RELATION a inner join country b on (a.id = b.id)  
5. start with a.parentid is NULL connect by prior a.id = a.PARENTID  
6. ) T where T."isleaf" = 1; 
7.  
8.  
9. NAME 
10. -------------------------------------------------- 
11. Canada 
12. Central America 
13. Island Nations 
14. Alabama 
15. Alaska 
16. Arizona 
17. Arkansas 
18. California 
19. South America 
20. Europe 
21. Asia 
22. Africa 
23. Australia 
24.  
25. --已选择13行。 
26.  
27. --已用时间: 00: 00: 00.01 

3).查看ROOT节点,代码如下:

1. select connect_by_root b.name 
2. from COUNTRY_RELATION a inner join country b on (a.id = b.id)  
3. start with a.parentid is NULL connect by a.id = a.PARENTID  
4. --Vevb.com 
5. CONNECT_BY_ROOTB.NAME 
6. -------------------------------------------------- 
7. Earth 
8.  
9. --已用时间: 00: 00: 00.01 

4).查看路径,代码如下:

1. select sys_connect_by_path(b.name,'/') "path"  
2. from COUNTRY_RELATION a inner join country b on (a.id = b.id)  
3. start with a.parentid is NULL connect by prior a.id = a.PARENTID  
4. order by level,a.id; 
5.  
6. path 
7. -------------------------------------------------- 
8. /Earth 
9. /Earth/North America 
10. /Earth/South America 
11. /Earth/Europe 
12. /Earth/Asia 
13. /Earth/Africa 
14. /Earth/Australia 
15. /Earth/North America/Canada 
16. /Earth/North America/Central America 
17. /Earth/North America/Island Nations 
18. /Earth/North America/United States 
19. /Earth/North America/United States/Alabama 
20. /Earth/North America/United States/Alaska 
21. /Earth/North America/United States/Arizona 
22. /Earth/North America/United States/Arkansas 
23. /Earth/North America/United States/California 
24.  
25. --已选择16行。 
26.  
27. --已用时间: 00: 00: 00.01 

接下来我们看看在MySQL 里面如何实现上面四种情形,前三种都比较简单,可以很容易写出SQL.

1)查看深度,代码如下:

1. mysql> SELECT COUNT(DISTINCT IFNULL(parentid,-1)) AS LEVEL FROM country_relation 
2. ; 
3. +-------+ 
4. | LEVEL | 
5. +-------+ 
6. | 4 | 
7. +-------+ 
8. 1 row in set (0.00 sec) 

2)查看ROOT节点,代码如下:

1. mysql> SELECT b.`name` AS root_node FROM 
2.     -> ( 
3.     -> SELECT id FROM country_relation WHERE parentid IS NULL 
4.     -> ) AS a, country AS b WHERE a.id = b.id; 
5. +-----------+ 
6. | root_node | 
7. +-----------+ 
8. | Earth | 
9. +-----------+ 
10. 1 row in set (0.00 sec) 

3).查看叶子节点,代码如下:

1. mysql> SELECT b.`name` AS leaf_node FROM 
2.     -> ( 
3.     -> SELECT id FROM country_relation WHERE id NOT IN (SELECT IFNULL(parentid, 
4. -1) FROM country_relation) 
5.     -> ) AS a, country AS b WHERE a.id = b.id; 
6. +-----------------+ 
7. | leaf_node | 
8. +-----------------+ 
9. | South America | 
10. | Europe | 
11. | Asia | 
12. | Africa | 
13. | Australia | 
14. | Canada | 
15. | Central America | 
16. | Island Nations | 
17. | Alabama | 
18. | Alaska | 
19. | Arizona | 
20. | Arkansas | 
21. | California | 
22. +-----------------+ 
23. 13 rows in set (0.00 sec) 
24.  
25.  
26. mysql> 

4)查看路径

这一块没有简单的SQL实现,不过可以用MySQL的存储过程来实现同样的功能,存储过程代码如下:

1. DELIMITER $$ 
2. USE `t_girl`$$ 
3. DROP PROCEDURE IF EXISTS `sp_show_list`$$ 
4. CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_show_list`() 
5. BEGIN 
6.       -- Created by ytt 2014/11/04. 
7.       -- Is equal to oracle's connect by syntax. 
8.       -- Body. 
9.       DROP TABLE IF EXISTS tmp_country_list; 
10.       CREATE TEMPORARY TABLE tmp_country_list (node_level INT UNSIGNED NOT NULL, node_path VARCHAR(1000) NOT NULL); 
11.       -- Get the root node. 
12.       INSERT INTO tmp_country_list SELECT 1, CONCAT('/',id) FROM country_relation WHERE parentid IS NULL; 
13.       -- Loop within all parent node. 
14.       cursor1:BEGIN 
15.         DECLARE done1 INT DEFAULT 0; 
16.         DECLARE i1 INT DEFAULT 1; 
17.         DECLARE v_parentid INT DEFAULT -1; 
18.         DECLARE v_node_path VARCHAR(1000) DEFAULT ''; 
19.         DECLARE cr1 CURSOR FOR SELECT parentid FROM country_relation WHERE parentid IS NOT NULL GROUP BY parentid ORDER BY parentid ASC; 
20.         DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = 1; 
21.          
22.         OPEN cr1; 
23.          
24.         loop1:LOOP 
25.           FETCH cr1 INTO v_parentid; 
26.           IF done1 = 1 THEN  
27.             LEAVE loop1; 
28.           END IF; 
29.           SET i1 = i1 + 1; 
30.            
31.           label_path:BEGIN 
32.             DECLARE done2 INT DEFAULT 0; 
33.             DECLARE CONTINUE HANDLER FOR NOT FOUND SET done2 = 1; 
34.             -- Get the upper path. 
35.             SELECT node_path FROM tmp_country_list WHERE node_level = i1 - 1 AND LOCATE(v_parentid,node_path) > 0 INTO v_node_path; 
36.             -- Escape the outer not found exception. 
37.             IF done2 = 1 THEN 
38.               SET done2 = 0; 
39.             END IF; 
40.             INSERT INTO tmp_country_list 
41.             SELECT i1,CONCAT(IFNULL(v_node_path,''),'/',id) FROM country_relation WHERE parentid = v_parentid; 
42.           END; 
43.         END LOOP; 
44.          
45.         CLOSE cr1; 
46.          
47.       END; 
48.       -- Update node's id to its real name. 
49.       update_name_label:BEGIN 
50.         DECLARE cnt INT DEFAULT 0; 
51.         DECLARE i2 INT DEFAULT 0; 
52.         SELECT MAX(node_level) FROM tmp_country_list INTO cnt; 
53.         WHILE i2 < cnt 
54.         DO 
55.           UPDATE tmp_country_list AS a, country AS b  
56.           SET a.node_path = REPLACE(a.node_path,CONCAT('/',b.id),CONCAT('/',b.name)) 
57.           WHERE LOCATE(CONCAT('/',b.id),a.node_path) > 0; 
58.           SET i2 = i2 + 1; 
59.         END WHILE; 
60.       END; 
61.       
62.      SELECT node_path FROM tmp_country_list; 
63.     END$$ 
64.  
65.  
66. DELIMITER ; 

调用结果,代码如下:

1. mysql> CALL sp_show_list(); 
2. +-----------------------------------------------+ 
3. | node_path | 
4. +-----------------------------------------------+ 
5. | /Earth | 
6. | /Earth/North America | 
7. | /Earth/South America | 
8. | /Earth/Europe | 
9. | /Earth/Asia | 
10. | /Earth/Africa | 
11. | /Earth/Australia | 
12. | /Earth/North America/Canada | 
13. | /Earth/North America/Central America | 
14. | /Earth/North America/Island Nations | 
15. | /Earth/North America/United States | 
16. | /Earth/North America/United States/Alabama | 
17. | /Earth/North America/United States/Alaska | 
18. | /Earth/North America/United States/Arizona | 
19. | /Earth/North America/United States/Arkansas | 
20. | /Earth/North America/United States/California | 
21. +-----------------------------------------------+ 
22. 16 rows in set (0.04 sec) 
23.  
24. Query OK, 0 rows affected (0.08 sec) 
25.  
26. mysql>