MySQL enables Oracle start with connect by recursion

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

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

But many times we can't control the depth of the tree. You will need to implement this recursively in MySQL using stored procedures or in your program. This article discusses several approaches to implementation.

Sample data:

Mysql> createtable 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 |
+----+----------+------+
$ rows in Set (0.00 sec)

Tree diagrams are 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 .

Create a function Getchildlst to get a string of all child node numbers.

Mysql> delimiter//
Mysql>
mysql> CREATE FUNCTION ' getchildlst ' (rootidint)
Returnsvarchar (1000)
BEGIN
DECLARE Stempvarchar (1000);
Declarestempchd VARCHAR (1000);
-
SET stemp = ' $ ';
SET Stempchd=cast (Rootid as CHAR);
-
-WHILESTEMPCHD is not a null do
-SET stemp =concat (stemp, ', ', stempchd);
-Selectgroup_concat (ID) into Stempchd from TreeNodes wherefind_in_set (PID,STEMPCHD) >0;
Endwhile;
Returnstemp;
-END
//
Query OK, 0 rows Affected (0.00 sec)

Mysql>
Mysql> delimiter;

Use the Find_in_set function directly with this getchildlst to find

Mysql> selectgetchildlst (1);
+-----------------+
| Getchildlst (1) |
+-----------------+
| $,1,2,3,4,5,6,7 |
+-----------------+
1 row in Set (0.00 sec)

Mysql> SELECT * from TreeNodes
-Wherefind_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
-Wherefind_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, convenient, no recursive call hierarchy depth limit (max_sp_recursion_depth, maximum 255);

disadvantage : The length is limited, although can enlarge Returnsvarchar (1000), but always have the maximum limit.

Recursive invocation of function is not yet supported in MySQL current version (5.1.33-community).

Method two: Using temporal tables and process recursion

Create the stored procedure as follows. Createchildlst is a recursive process, showchildlst to invoke the entry process, prepare temporary tables and initialize.

Mysql> delimiter//
Mysql>
Mysql> #Entrance process
Mysql> CREATE PROCEDURE showchildlst (in Rootidint)
BEGIN
CREATE temporary TABLE IF notexists tmplst
-Sno intprimary key auto_increment,id int,depth int);
DELETE Fromtmplst;
-
Callcreatechildlst (rootid,0);
-
-Selecttmplst.*,treenodes.* from Tmplst,treenodes wheretmplst.id=treenodes.id order by Tmplst.sno;
-END;
//
Query OK, 0 rows Affected (0.00 sec)

Mysql>
Mysql> # recursive procedure
Mysql> createprocedure createchildlst (in Rootid int,in ndepth INT)
    BEGIN
   ->  declare doneint DEFAULT 0;
   ->  declare BINT;
   ->  declare cur1cursor for SELECT ID from treenodes where pid=rootid;
   ->  declarecontinue HANDLER for not FOUND SET do = 1;
   ->
   ->  insert intotmplst values (null,rootid,ndepth);

-
OPENcur1;
-
--FETCH Cur1into B;
While Done=0do
Callcreatechildlst (b,ndepth+1);
--FETCH Cur1into B;
Endwhile;
-
CLOSEcur1;
-END;
//
Query OK, 0 rows Affected (0.00 sec)
Mysql> delimiter;

Incoming nodes when called

Mysql> callshowchildlst (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)

The depth is a depth, so that it can be formatted for some display on the program. Similar to level pseudo-columns in Oracle. Sno is for sort control only. This allows you to join queries with other tables in the database through temporary table Tmplst.

In MySQL you can use the system parameter max_sp_recursion_depth to control the maximum number of layers for recursive calls. The following example is set to 12.

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

Advantages: can be more flexible processing, and the display of the number of layers. And the results can be obtained according to the tree traversal order.

Cons: recursion has a limit of 255.

Method Three: Using intermediate tables and procedures

(This method is adapted by yongyupost2000)

Create the stored procedure as follows. Because MySQL does not allow multiple references to temporary tables in the same statement, it is only implemented using the normal table Tmplst. Of course your program is responsible for clearing this table when you are done using it.

delimiter//

Drop PROCEDURE ifexists showtreenodes_yongyupost2000//

CREATE procedureshowtreenodes_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 fromtreenodes WHERE Pid=rootid;
While Row_count () >0 do
SET level=level+1;
INSERT into Tmplst
Selecta.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

callshowtreenodes_yongyupost2000 (0);

After execution, a tmplst table is generated, Nlevel is the node depth, and Scort is the sort field.
How to use

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 |
+--------------------------------------------+
$ rows in Set (0.00 sec)

MySQL enables Oracle start with connect by recursion