如何處理MySQL/Oracle鄰接模型樹形結(jié)構(gòu)問題

下文給大家?guī)黻P(guān)于如何處理MySQL/Oracle鄰接模型樹形結(jié)構(gòu)問題，感興趣的話就一起來看看這篇文章吧，相信看完如何處理MySQL/Oracle鄰接模型樹形結(jié)構(gòu)問題對大家多少有點幫助吧。

數(shù)據(jù)庫對層次結(jié)構(gòu)的處理模型有好多種，可以根據(jù)自己的需求來設(shè)計模型，當(dāng)然最簡單的也是最容易設(shè)計的模型就是所謂的鄰接模型。在這方面，其他數(shù)據(jù)庫比如Oracle 提供了現(xiàn)成的分析方法 connect by，而MySQL在這方面就顯得有些薄弱了。 不過可以用MySQL的存儲過程實現(xiàn)ORACLE類似的分析功能

這樣，先來創(chuàng)建一個簡單的數(shù)表。

create table country ( id number(2) not null,  name varchar(60) not null);
create table country_relation (id number(2),  parentid number(2));

插入一些數(shù)據(jù)

-- 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 里面，對這些操作就比較簡單了，都是系統(tǒng)提供的。

比如下面四種情形：

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）. 查看葉子節(jié)點

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節(jié)點

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 里面如何實現(xiàn)上面四種情形：

前三種都比較簡單，可以很容易寫出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節(jié)點

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).  查看葉子節(jié)點

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實現(xiàn)，不過可以用MySQL的存儲過程來實現(xiàn)同樣的功能。

存儲過程代碼如下：

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 ;

調(diào)用結(jié)果：

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>

看了以上關(guān)于如何處理MySQL/Oracle鄰接模型樹形結(jié)構(gòu)問題詳細內(nèi)容，是否有所收獲。如果想要了解更多相關(guān)，可以繼續(xù)關(guān)注我們的行業(yè)資訊板塊。