当前位置：首页 > Mysql教程 > 列表

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

发布:smiling　来源: PHP粉丝网 　添加日期：2015-04-20 15:10:50　浏览: 评论：0　

项目需求用到了邻接模型,但是是采用开源的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. --phpfensi.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>

Tags: MySQL存储过程 Oracle邻接模型

分享到：

推荐文章

热门文章

最新评论文章