Mysql realize the tree recursive query

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

In the Oracle we know there is a Hierarchical Queries by CONNECT BY we can easily check all of the current node all the child nodes. Unfortunately, in the current version of MySQL has no corresponding function.    

  

In MySQL if it is a limited level, for example, we can determine in advance if maximum depth of the tree is 4, all node is the root of the tree depth is not more than 4, we can directly through the left to achieve join.   

 

But most of the time we are unable to control the depth of the tree. Then you need to use MySQL in stored procedures or in your program to achieve this recursion. This paper discusses several implementation methods.   

  

Sample data:   

mysql> create table treeNodes  

 (  id int primary key, nodename varchar(20), pid int );  

-> Query OK, 0 rows affected (0.09 sec)   

mysql> select * from treenodes;  

+----+----------+------+  

| id | nodename | pid  |  

+----+----------+------+  

|  1 | A        |    0 |  

|  2 | B        |    1 |  

|  3 | C        |    1 |  

|  4 | D        |    2 |  

|  5 | E        |    2 |  

|  6 | F        |    3 |  

|  7 | G        |    6 |  

|  8 | H        |    0 |  

|  9 | I        |    8 |  

| 10 | J        |    8 |  

| 11 | K        |    8 |  

| 12 | L        |    9 |  

| 13 | M        |    9 |  

| 14 | N        |   12 |  

| 15 | O        |   12 |  

| 16 | P        |   15 |  

| 17 | Q        |   15 |  

+----+----------+------+  

17 rows in set (0.00 sec)  

 

The tree diagram as follows making making

 1:A  

 +-- 2:B  

  |    +-- 4:D  

  |    +-- 5:E  

  +-- 3:C  

       +-- 6:F  

            +-- 7:G  

 8:H  

  +-- 9:I  

  |    +-- 12:L  

  |    |    +--14:N  

  |    |    +--15:O  

  |    |        +--16:P  

  |    |        +--17:Q  

  |    +-- 13:M  

  +-- 10:J  

  +-- 11:K       

Method: using the function to get all the child nodes.   

  

Create a function getChildLst, get a composed of all the sub node number string. Making making making making

mysql> delimiter //  

mysql>  

mysql> CREATE FUNCTION `getChildLst`(rootId INT)  

     RETURNS varchar(1000)  

    BEGIN  

      DECLARE sTemp VARCHAR(1000);  

       DECLARE sTempChd VARCHAR(1000);  

    

      SET sTemp = '$';  

      SET sTempChd =cast(rootId as CHAR);  

      

       WHILE sTempChd is not null DO  

         SET sTemp = concat(sTemp,',',sTempChd);  

         SELECT group_concat(id) INTO sTempChd FROM treeNodes where FIND_IN_SET(pid,sTempChd)>0;  

    END WHILE;  

    RETURN sTemp;  

     END  

    //  

Query OK, 0 rows affected (0.00 sec)   

mysql>  

mysql> delimiter ;  

 

We use the direct use of find_in_set function with the getChildlst to find making

mysql> select getChildLst(1);  

+-----------------+  

| getChildLst(1)  |  

+-----------------+  

| $,1,2,3,4,5,6,7 |  

+-----------------+  

1 row in set (0.00 sec)     

mysql> select * from treeNodes  

    -> where FIND_IN_SET(id, getChildLst(1));  

+----+----------+------+  

| id | nodename | pid  |  

+----+----------+------+  

|  1 | A        |    0 |  

|  2 | B        |    1 |  

|  3 | C        |    1 |  

|  4 | D        |    2 |  

|  5 | E        |    2 |  

|  6 | F        |    3 |  

|  7 | G        |    6 |  

+----+----------+------+  

7 rows in set (0.01 sec)  

mysql> select * from treeNodes  

    -> where FIND_IN_SET(id, getChildLst(3));  

+----+----------+------+  

| id | nodename | pid  |  

+----+----------+------+  

|  3 | C        |    1 |  

|  6 | F        |    3 |  

|  7 | G        |    6 |  

+----+----------+------+  

3 rows in set (0.01 sec)  

  

Advantages: simple and convenient,, limit no recursion level depth (max_sp_recursion_depth, 255),     

Disadvantages: length is limited, can expand RETURNS varchar (1000), but there is always the maximum limit.    

The current version (5.1.33-community MySQL) recursive call does not support function.   

 

Two methods: the use of temporary tables and recursive making

Create a stored procedure is as follows. CreateChildLst as a recursive process, showChildLst calls the entrance process, prepare a temporary table and initialization.   

mysql> delimiter //  

mysql>  

mysql> #The process of making entrance

mysql> CREATE PROCEDURE showChildLst (IN rootId INT)  

     BEGIN  

      CREATE TEMPORARY TABLE IF NOT EXISTS tmpLst   

       (sno int primary key auto_increment,id int,depth int);  

      DELETE FROM tmpLst;  

     

     CALL createChildLst(rootId,0);  

     select tmpLst.*,treeNodes.* from tmpLst,treeNodes where tmpLst.id=treeNodes.id order by tmpLst.sno;  

     END;  

 //  

Query OK, 0 rows affected (0.00 sec)  

 

mysql>  

mysql> #Recursive procedure making

mysql> CREATE PROCEDURE createChildLst (IN rootId INT,IN nDepth INT)  

     BEGIN  

      DECLARE done INT DEFAULT 0;  

      DECLARE b INT;  

      DECLARE cur1 CURSOR FOR SELECT id FROM treeNodes where pid=rootId;  

      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;  

 

     insert into tmpLst values (null,rootId,nDepth);   

      OPEN cur1;  

      FETCH cur1 INTO b;  

     WHILE done=0 DO  

          CALL createChildLst(b,nDepth+1);  

         FETCH cur1 INTO b;  

    END WHILE;  

    CLOSE cur1;  

    END;  

    //  

   Query OK, 0 rows affected (0.00 sec)   

 mysql> delimiter ;   

 

Called node making

 

mysql> call showChildLst(1);  

+-----+------+-------+----+----------+------+  

| sno | id   | depth | id | nodename | pid  |  

+-----+------+-------+----+----------+------+  

|   4 |    1 |     0 |  1 | A        |    0 |  

|   5 |    2 |     1 |  2 | B        |    1 |  

|   6 |    4 |     2 |  4 | D        |    2 |  

|   7 |    5 |     2 |  5 | E        |    2 |  

|   8 |    3 |     1 |  3 | C        |    1 |  

|   9 |    6 |     2 |  6 | F        |    3 |  

|  10 |    7 |     3 |  7 | G        |    6 |  

+-----+------+-------+----+----------+------+  

7 rows in set (0.13 sec)  

  

Query OK, 0 rows affected, 1 warning (0.14 sec)  

  

mysql>  

mysql> call showChildLst(3);  

+-----+------+-------+----+----------+------+  

| sno | id   | depth | id | nodename | pid  |  

+-----+------+-------+----+----------+------+  

|   1 |    3 |     0 |  3 | C        |    1 |  

|   2 |    6 |     1 |  6 | F        |    3 |  

|   3 |    7 |     2 |  7 | G        |    6 |  

+-----+------+-------+----+----------+------+  

  

3 rows in set (0.11 sec)  

  

Query OK, 0 rows affected, 1 warning (0.11 sec)  

  

Depth depth, which can make some display formatted processing in the program. Level pseudo column is similar to the oracle. SnO for sequence control. So you can also through the temporary table in the database of tmpLst and other table join query.   

 

MySQL you can use max_sp_recursion_depth to control the system parameters recursive call number limit. The following example set to 12 making

 

mysql> set max_sp_recursion_depth=12;  

Query OK, 0 rows affected (0.00 sec)  

 

 

Advantages: can be more flexible processing, display and layers. And we can get results according to the sequential traversal of tree.   

 

Disadvantages: recursive 255 limits.   

 

Method three: the middle table and process of making

 

(this method provided by yongyupost2000 way adapted) making

  

Create a stored procedure is as follows. In the same statement on temporary tables referenced multiple times MySQL is not allowed, only to use the common form of tmpLst to achieve. Of course, your program is responsible for clearing the table after use.   

  

 

delimiter //   

drop PROCEDURE IF EXISTS  showTreeNodes_yongyupost2000//  

 

CREATE PROCEDURE showTreeNodes_yongyupost2000 (IN rootid INT)  

BEGIN  

 DECLARE Level int ;  

 drop TABLE IF EXISTS tmpLst;  

 CREATE TABLE tmpLst (  

 id int,  

nLevel int,  

sCort varchar(8000)  

 );  

 

 Set Level=0 ;  

INSERT into tmpLst SELECT id,Level,ID FROM treeNodes WHERE PID=rootid;  

WHILE ROW_COUNT()>0 DO  

 SET Level=Level+1 ;  

INSERT into tmpLst   

 SELECT A.ID,Level,concat(B.sCort,A.ID) FROM treeNodes A,tmpLst B   

    WHERE  A.PID=B.ID AND B.nLevel=Level-1  ;  

 END WHILE;  

    

END;  

//  

 

delimiter ;  

  

CALL showTreeNodes_yongyupost2000(0);  

  

After the implementation will produce a tmpLst table, nLevel for the depth of the node, sCort to sort field.   

Using the method of making

  

  

SELECT concat(SPACE(B.nLevel*2),'+--',A.nodename)  

FROM treeNodes A,tmpLst B   

WHERE A.ID=B.ID   

ORDER BY B.sCort;  

 

+--------------------------------------------+  

| concat(SPACE(B.nLevel*2),'+--',A.nodename) |  

+--------------------------------------------+  

| +--A                                       |  

|   +--B                                     |  

|     +--D                                   |  

|     +--E                                   |  

|   +--C                                     |  

|     +--F                                   |  

|       +--G                                 |  

| +--H                                       |  

|   +--J                                     |  

|   +--K                                     |  

|   +--I                                     |  

|     +--L                                   |  

|       +--N                                 |  

|       +--O                                 |  

|         +--P                               |  

|         +--Q                               |  

|     +--M                                   |  

+--------------------------------------------+  

17 rows in set (0.00 sec)  

  

Advantages: display layers. And we can get results according to the sequential traversal of tree. No recursive limit.   

Disadvantages: restrictions on temporary tables MySQL, can only use an ordinary table, need to clean up

  

 

These are a few stored procedures used in the MySQL to achieve a relatively simple method.   

Reprinted from: ,

             

             

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Roderick at December 10, 2013 - 6:19 PM