Mysql implementation of tree-like all child node Query method _mysql

This article describes the MySQL implementation of tree-like all child node Query method. Share to everyone for your reference, specific as follows:

In Oracle we know that there is a hierarchical Queries through connect by we can easily check all the child nodes under the current node. Unfortunately, there is no corresponding functionality in the current version of MySQL.

In MySQL if it is a limited level, for example, if we can determine the maximum depth of the tree is 4, then all nodes are rooted tree is not more than 4 depth, then we can directly through the left join to achieve.

But most of the time we can't control the depth of the tree. You need to implement this recursion in MySQL with stored procedures or in your program. This article discusses several methods of implementation.

Sample data:

Mysql> CREATE TABLE TreeNodes
  -> (
  -> ID int primary KEY,
  -> nodename varchar (),
  -> pid I NT
  ->);
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    |  |
| 15 | O    |  |
| 16 | P    |  |
| 17 | Q    |  |
+----+----------+------+
rows in Set (0.00 sec)

Tree is as follows

 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 One: Use the function to get all the child node numbers.

Creates a function Getchildlst and gets a string of all the child node numbers.

Mysql> delimiter//
mysql>
mysql> CREATE FUNCTION ' getchildlst ' (Rootid INT)
  -> RETURNS (1000)
  -> BEGIN
  ->  DECLARE stemp VARCHAR (1000);
  ->  DECLARE stempchd VARCHAR (1000);
  ->
  ->  SET stemp = ' $ ';
  ->  SET stempchd =cast (Rootid as CHAR);
  ->
  ->  while stempchd isn't 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>
;

Using the Find_in_set function we use directly to match this getchildlst to find

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, getc Hildlst (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_se T (ID, Getchildlst (3));
+----+----------+------+
| id | nodename | pid
| +----+----------+------+
| 3 | C    |  1 |
| 6 | F    |  3 |
| 7 | G    |  6 |
+----+----------+------+
3 rows in Set (0.01 sec)

Advantages: Simple, convenient, no recursive call level depth limit (max_sp_recursion_depth, Max 255);

Disadvantage: The length is limited, although can enlarge RETURNS varchar (1000), but always have the biggest limit.

Recursive calls to function are not supported in the current version of MySQL (5.1.33-community).

Method two: Using temporary tables and procedures recursively

The stored procedures are created as follows. Createchildlst is a recursive procedure, Showchildlst prepares a temporary table and initializes it for the calling entry process.

Mysql> delimiter//mysql> mysql> # Portal Procedure mysql> CREATE PROCEDURE showchildlst (in Rootid INT)-> BEGIN
  -> CREATE Temporary TABLE IF not EXISTS tmplst-> (sno int primary key auto_increment,id 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 mysql> CREATE PROCEDURE createchildlst (in Roo
  tId 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 does-> call Createchildlst (b,ndepth+1); ->    FETCH Cur1 into B;
  -> End While;
  ->-> Close Cur1;
  -> end;

 ->//Query OK, 0 rows Affected (0.00 sec) mysql> delimiter;

Incoming nodes at call time

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 |
| Ten |  7 |   3 | 7 | G    |  6 |
+-----+------+-------+----+----------+------+
7 rows in Set (0.13 sec)
Query OK, 0 rows affected, 1 warning (0.1 4 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.1 1 sec)

Depth is a depth so that you can format the program for some display. Similar to the level pseudo column in Oracle. Sno is for sort control only. This way you can also join queries with other tables in the database through a temporary table tmplst.

In MySQL, you can use system parameter max_sp_recursion_depth to control the upper limit of the number of recursive calls. The following example is set to 12.

Mysql> set max_sp_recursion_depth=12;
Query OK, 0 rows Affected (0.00 sec)

Advantages: Can be more flexible processing, and the number of layers display. And you can get the results in the tree's traversal order.

Disadvantage: Recursion has a limit of 255.

Method III: Using intermediate tables and processes

(This method is provided by yongyupost2000)
The stored procedures are created as follows. Because MySQL does not allow multiple references to temporary tables in the same statement, it is only possible to use plain table tmplst. Of course, your program is responsible for cleaning up the table after you run out of it.

Delimiter//
drop PROCEDURE IF EXISTS showtreenodes_yongyupost2000//
CREATE PROCEDURE Showtreenodes_ yongyupost2000 (in Rootid int) The
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 execution, a tmplst table is generated, Nlevel is the node depth, and Scort is the sorted field.

How to use

SELECT concat (b.nlevel*2), ' +--', a.nodename) from
treenodes a,tmplst B
WHERE a.id=b.id
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                  |
+--------------------------------------------+
rows in Set (0.00 sec)

Advantages: The display of layer number. And you can get the results in the tree's traversal order. There is no recursive restriction.

Disadvantages: MySQL in the temporary table restrictions, can only use the ordinary table, you need to do after clean-up.

These are some of the more simple ways to implement stored procedures in MySQL.

More information about MySQL interested readers can view the site topics: "MySQL Log operation skills Daquan", "MySQL Transaction operation skills Summary", "MySQL stored process skills encyclopedia", "MySQL database lock related skills summary" and "MySQL commonly used function large summary"

I hope this article will help you with the MySQL database meter.