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

发布时间：2022-06-28 14:06 所属栏目：115 来源：互联网

导读：项目需求用到了邻接模型,但是是采用开源的Mysql,而Mysql没有这个功能,Oracle数据库提供了现在的分析方法 connect by 处理邻接模型,不过mysql支持存储过程,可以建立存储过程实现Oracle的分析功能. 数据库对层次结构的处理模型有好多种,可以根据自己的需求来设

　　项目需求用到了邻接模型,但是是采用开源的Mysql,而Mysql没有这个功能,Oracle数据库提供了现在的分析方法 connect by 处理邻接模型,不过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));
　　
　　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
　　--phpfensi.com
　　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>。

（编辑：ASP站长网）