"Original" MySQL simulation Oracle adjacency Model tree Processing

Database on the hierarchy of the processing model has a lot of, can according to their own needs to design models, of course, the simplest is the most easily designed model is called adjacency model. In this regard, other databases such as Oracle provide a ready-made analysis method for connect by, and MySQL is a bit weak in this regard. However, Oracle's similar analytics can be implemented with MySQL stored procedures

In this way, first create a simple tables.

CREATE TABLE Country (ID number (2) not NULL, name varchar is NOT NULL), CREATE TABLE Country_relation (ID number (2), PA Rentid number (2));

Insert some data

-- 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  (Ten, ' Island nations ');insert into country  ( Id,name)  values  (one, ' united states ');insert into country  (id,name)  values   (' Alabama '); insert into country  (Id,name)  values  (Alaska ');insert into country  (ID, Name)  values  (Arizona ');insert into country  (id,name)  values  (15, ' Arkansas ');insert into country  (id,name)  values  (+, ' 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);

In Oracle, these operations are relatively straightforward and are provided by the system.

For example, the following four scenarios:

1). View depth,

Select Max (level) ' level ' from country_relation a start with A.parentid are nullconnect by PRIOR a.id = A.parentidorder by     Level Level----------4 time: 00:00:00.03

2). View leaf nodes

Select name from (select B.name, connect_by_isleaf "IsLeaf" from Country_relation a inner joins country B on (a.id = b.id) s Tart with A.parentid are NULL connect by prior a.id = A.parentid) T where T. " IsLeaf "= 1; NAME--------------------------------------------------canadacentral Americaisland Nationsalabamaalaskaarizonaarkansascaliforniasouth Americaeuropeasiaafricaaustralia has selected 13 rows. Time used: 00:00:00.01

3) View the root node

Select Connect_by_root b.namefrom country_relation a INNER join country B on (a.id = b.id) Start with A.parentid is NULL C Onnect by a.id = A.parentid CONNECT_BY_ROOTB. NAME--------------------------------------------------Earth Time: 00:00:00.01

4). View Path

Select Sys_connect_by_path (B.name, '/') "path" from Country_relation a inner joins country B on (a.id = b.id) Start with A.P Arentid is a 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/ 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 has selected 16 rows. Time used: 00:00:00.01

Let's look at how the above four scenarios are implemented in MySQL:

The first three are relatively simple and can be easily written out in SQL.

1) View Depth

Mysql> SELECT COUNT (DISTINCT ifnull (parentid,-1)) as level from country_relation;+-------+|     Level |+-------+| 4 |+-------+1 row in Set (0.00 sec

)

2) View the root node

Mysql> Select B. ' Name ' as Root_node from (-a 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). View leaf nodes

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  | | &nbSp alabama         | |  alaska          | |  arizona         | |  arkansas        | |  california      |+-----------------+13 rows in set  ( 0.00 SEC) mysql>

4) View Path

This piece does not have a simple SQL implementation, but you can use the MySQL stored procedure to achieve the same functionality.

The stored procedure code is as follows:

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 ( 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 ( 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  ;

Call Result:

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>

This article is from "God, we don't see!" "Blog, be sure to keep this provenance http://yueliangdao0608.blog.51cto.com/397025/1571772

"Original" MySQL simulation Oracle adjacency Model tree Processing