Query all Tree nodes in MySQL

From: http://blog.csdn.net/ACMAIN_CHM/archive/2009/05/02/4142971.aspx

In Oracle, we know that there is a hierarchical queries. Using connect by, we can easily query all the subnodes under all the current nodes. However, it is a pity that there are no corresponding functions in the current MySQL version.

In MySQL, if there is a limited hierarchy, for example, if we can determine that the maximum depth of this tree is 4, then the depth of all trees with nodes as the root will not exceed 4, then we can directly implement it through left join.

However, we often cannot control the depth of the tree. In this case, you need to implement this recursion using a stored procedure in MySQL or in your program. This article 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 structure is as follows:

1:
+ -- 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 1: Use a function to obtain all subnode numbers..

Create a function getchildlst to obtain a string consisting of all child node numbers.

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 can use the find_in_set function together with the 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, 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. There is no limit on the Level Depth of recursive calls (max_sp_recursion_depth, up to 255 );

Disadvantages: The length is limited. Although the returns varchar (1000) can be expanded, there is always a maximum limit.

Currently, MySQL 5.1.33-community does not support recursive function calls.

Method 2: use temporary tables and recursive procedures

The stored procedure is as follows. Createchildlst is a recursive process, showchildlst is a call entry process, and a temporary table and initialization are prepared.

Mysql> delimiter //
Mysql>
Mysql> # entry process
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 process
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;

Incoming Node during call

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 is the depth, which allows you to format the display in the program. Similar to level pseudo columns in Oracle. Sno is only used for sorting control. In this way, you can use the temporary table tmplst to join other tables in the database for query.

In MySQL, you can use the system parameter max_sp_recursion_depth to control the maximum number of layers 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:It allows more flexible processing and display of layers. In addition, results can be obtained in the order of tree traversal.

Disadvantages:Recursion is limited to 255.

Method 3: Use the intermediate table and Process

(This method is adapted from yongyupost2000)

The stored procedure is as follows. Since MySQL does not allow multiple references to temporary tables in the same statement, it is only implemented using the common table tmplst. 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 execution, a tmplst table is generated. nlevel indicates the node depth, and Scort indicates the sorting field.
Usage

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: Displays the number of layers. In addition, results can be obtained in the order of tree traversal. There are no recursive restrictions.
Disadvantages: Restrictions on temporary tables in MySQL. Only common tables can be used. You need to clean up the temporary tables.

The above are some simple implementation methods for using stored procedures in MySQL.