如何处理MySQL/Oracle邻接模型树形结构问题

下文给大家带来关于如何处理MySQL/Oracle邻接模型树形结构问题，感兴趣的话就一起来看看这篇文章吧，相信看完如何处理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 NULLconnect by PRIOR a.id = a.PARENTIDorder 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--------------------------------------------------CanadaCentral AmericaIsland NationsAlabamaAlaskaArizonaArkansasCaliforniaSouth AmericaEuropeAsiaAfricaAustralia已选择13行。已用时间:  00: 00: 00.01

3） 查看ROOT节点

select connect_by_root b.namefrom 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>

看了以上关于如何处理MySQL/Oracle邻接模型树形结构问题详细内容，是否有所收获。如果想要了解更多相关，可以继续关注我们的行业资讯板块。