[Original] tree-like processing of Oracle neighbor model simulation in MySQL

Databases have many processing models for hierarchies. You can design models based on your needs. Of course, the simplest and easiest model to design is the so-called adjacent model. In this regard, other databases, such as Oracle, provide the ready-made analysis method connectby, while MySQL seems a little weak in this regard. However, you can use MySQL to store

Databases have many processing models for hierarchies. You can design models based on your needs. Of course, the simplest and easiest model to design is the so-called adjacent model. In this regard, other databases, such as Oracle, provide the ready-made analysis method connect by, while MySQL seems a little weak in this regard. However, you can use MySQL to store

Databases have many processing models for hierarchies. You can design models based on your needs. Of course, the simplest and easiest model to design is the so-called adjacent model. In this regard, other databases, such as Oracle, provide the ready-made analysis method connect by, while MySQL seems a little weak in this regard. However, you can use the MySQL stored procedure to implement similar ORACLE analysis functions.

In this way, create a simple data 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 (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);

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

For example, the following four situations:

1). view the depth,

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

2). View leaf nodes

Select name from (select B. name, connect_by_isleaf "isleaf" from COUNTRY_RELATION a inner join country B on (. id = B. id) start with. parentid is NULL connect by prior. id =. PARENTID) T where T. "isleaf" = 1; NAME your CanadaCentral AmericaIsland nationsalabamaalaskaarizon#kansascaliforniasouth AmericaEuropeAsiaAfricaAustralia has selected 13 rows. Used time: 00: 00: 00.01

3) view the ROOT node

Select connect_by_root B. namefrom COUNTRY_RELATION a inner join country B on (. id = B. id) start with. parentid is NULL connect by. id =. PARENTID CONNECT_BY_ROOTB.NAME -------------------------------------------------- Earth time in use: 00: 00: 00.01

4). view the path

Select sys_connect_by_path (B. name, '/') "path" from COUNTRY_RELATION a inner join country B on (. id = B. id) start with. parentid is NULL connect by prior. id =. PARENTID order by level,. id; path ----------------------------------------------/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/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 16 rows selected. Used time: 00: 00: 00.01

Next, let's take a look at how to implement the above four situations in MySQL:

The first three methods are relatively simple and can easily write SQL statements.

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.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  || Alabama         || Alaska          || Arizona         || Arkansas        || California      |+-----------------+13 rows in set (0.00 sec)mysql>

4) view the path

There is no simple SQL implementation, but you can use the MySQL stored procedure to implement the same function.

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

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>