An example of the tree processing of Oracle adjacency model based on MySQL stored procedure

There are many kinds of database processing model of the hierarchy, can design the model according to own demand, of course, the simplest and easiest model is the so-called adjacency model. In this respect, other databases, such as Oracle, provide out-of-the-box analytical methods to connect by, and MySQL is somewhat weak in this regard. However, you can use MySQL's stored procedures to implement Oracle's similar analysis capabilities


So, first, create a simple table of tables.

The code is as follows	Copy Code
CREATE TABLE Country (ID number (2) is not NULL, name varchar is not NULL); CREATE TABLE Country_relation (ID number (2), ParentID number (2));

Insert some data

The code is as follows

Copy Code

--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, ' America '); INSERT into country (id,name) VALUES (' 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 (' 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 simpler and are provided by the system.
For example, the following four scenarios:
1). View depth,

The code is as follows	Copy Code
Select Max (level) ' level ' from country_relation A to start with A.parentid is NULL Connect by PRIOR a.id = A.parentid Order by level; Level ---------- 4 Time used: 00:00:00.03

2). View leaf nodes

code is as follows

copy code

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 are NULL connect by prior a.id = A.parentid ) t where T. " IsLeaf "= 1; NAME -------------------------------------------------- Canada America Island Nations Alabama Alaska Arizona Arkansas California South America Europe Asia africa< br> Australia has selected 13 rows. Time spent: 00:00:00.01

3) View root node

The code is as follows	Copy Code
Select Connect_by_root b.name From Country_relation a INNER join COUNTRY b on (a.id = b.id) Start with A.parentid are NULL connect by a.id = A.parentid Connect_by_rootb.name -------------------------------------------------- Earth Time used: 00:00:00.01

4). View Path

The code is as follows

Copy Code

Select Sys_connect_by_path (B.name, '/') "path" from Country_relation a inner join COUNTRY b in (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 has selected 16 rows. Time spent: 00:00:00.01

Next, let's look at how to implement the above four scenarios in MySQL:
The first three are simpler and can be easily written out of SQL.
1) View Depth

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

2) View root node

The code is as follows	Copy Code
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). View leaf nodes

code is as follows

copy code

mysql> SELECT B. ' Name ' as Leaf_node from    -> br>    -> 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 | | America | | Island Nations | | Alabama | | Alaska | | Arizona | | Arkansas | | California | +-----------------+ rows in Set (0.00 sec) Mysql>

4) View the path
There is no simple SQL implementation, but you can use MySQL's stored procedures to achieve the same functionality.
The stored procedure code is as follows:

The code is as follows

Copy Code

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 ('/', IDS) 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 are not NULL GROUP by ParentID ORDER by Parenti D 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 it 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 Todo 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:

The code is as follows

Copy Code

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 | +-----------------------------------------------+ Rows in Set (0.04 sec) Query OK, 0 rows affected (0.08 sec) Mysql>