How to deal with the Tree structure of MySQL/Oracle adjacency Model 01/29 Update SLTechnology News&Howtos

How to deal with the Tree structure of MySQL/Oracle adjacency Model

2025-01-29 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)06/01 Report--

The following brings you about how to deal with the tree structure of MySQL/Oracle adjacency model. If you are interested, let's take a look at this article. I believe it will be of some help to you after reading how to deal with the tree structure of MySQL/Oracle adjacency model.

There are many models for dealing with the hierarchical structure of the database, and you can design the model according to your own needs. Of course, the simplest and easiest model to design is the so-called adjacency model. In this regard, other databases such as Oracle provide an off-the-shelf analysis method connect by, while MySQL appears to be a bit weak in this respect. However, you can use MySQL's stored procedures to implement ORACLE-like analysis functions.

So, let's start by creating a simple table.

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

Insert some data

-Table country.insert into country (id,name) values; insert into country (id,name) values Insert into country (id,name) values (8 recorder Canada'); insert into country (id,name) values (9 recollection Central America'); insert into country (id,name) values (10 recollection Island Nations'); insert into country (id,name) values (11 minion United States'); insert into country (id,name) values (12 memorials Alabama'); insert into country (id,name) values (13 reparations Alaska'); insert into country (id,name) values (14 rewards Arizona') Insert into country (id,name) values (15 pensionary Arkansas); insert into country (id,name) values (16 recordings);-- Table country_relation.insert into country_relation (id,parentid) values (zero); insert into country_relation (id,parentid) values (2); insert into country_relation (id,parentid) values (3); insert into country_relation (id,parentid) values (4); insert into country_relation (id,parentid) values (5) Insert into country_relation (id,parentid) values (6); insert into country_relation (id,parentid) values (7); insert into country_relation (id,parentid) values (8); insert into country_relation (id,parentid) values (9); insert into country_relation (id,parentid) values (10); insert into country_relation (id,parentid) values (11); insert into country_relation (id,parentid) values (12); insert into country_relation (id,parentid) values (13) Insert into country_relation (id,parentid) values (14j011); insert into country_relation (id,parentid) values (15pc11); insert into country_relation (id,parentid) values (16j11)

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

For example, the following four situations:

1)。 View depth

Select max (level) "level" from COUNTRY_RELATION a start with a.parentid is NULLconnect by PRIOR a.id = a.PARENTIDorder by level; level- 4 elapsed time: 00: 00: 00.03

2)。 View Leaf Node

Select name from (select b.name, connect_by_isleaf "isleaf" from COUNTRY_RELATION an 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" = 1There are 13 lines that have been selected for the NAMEMurray CanadaCentral AmericaIsland NationsAlabamaAlaskaArizonaArkansasCaliforniaSouth AmericaEuropeAsiaAfricaAustralia. Time spent: 00: 00: 00.01

3) View the ROOT node

Select connect_by_root b.namefrom COUNTRY_RELATION an 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 elapsed time: 00: 00: 00.01

4)。 View path

Select sys_connect_by_path (b.name from COUNTRY_RELATION an inner join country b on') "path" from COUNTRY_RELATION an 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 has selected 16 lines. Time spent: 00: 00: 00.01

Let's take a look at how to implement the above four scenarios in MySQL:

The first three are relatively simple, and you can easily write 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-> (- > SELECT id FROM country_relation WHERE parentid IS NULL->) AS a, country AS b WHERE a.id = b.idittoscope sec + | root_node | +-+ | Earth | +-+ 1 row in set (0.00 sec)

3)。 View Leaf Node

4) View the path

There is no simple SQL implementation for this piece, but you can use MySQL's stored procedures to achieve the same functionality.

The stored procedure code is as follows:

DELIMITER $$USE `t _ list` $$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 (vastly parentid northbound path) > 0 INTO vastly nodeheaded path;-- Escape the outer not found exception. IF done2 = 1 THEN SET done2 = 0; END IF; INSERT INTO tmp_country_list SELECT i1 Magnum Concat (IFNULL (vicious nodewritten pathwriting'),'/', id) FROM country_relation WHERE parentid = vested 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:

Read the details of how to deal with the tree structure of the MySQL/Oracle adjacency model above, and whether you have gained anything. If you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.