MySQL recursive query

Sample data:

Create TableTreeNodes (ID int Primary Key, nodenamevarchar( -), PIDint);Select* fromtreenodes;+----+----------+------+|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||Ten| J |8|| One| K |8|| A| L |9|| -| M |9|| -| N | A|| the| O | A|| -| P | the|| -| Q | the|+----+----------+------+ - rows inch Set(0.00Sec

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  

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

Delimiter//CREATE FUNCTION' Getchildlst ' (rootidINT) RETURNSvarchar( +)BEGIN       DECLAREStempVARCHAR( +);DECLAREStempchdVARCHAR( +);SETStemp =' $ ';SETStempchd =cast (Rootid as CHAR); whileStempchd is  not NULLDoSETStemp =concat(Stemp,', ', STEMPCHD);SELECTGroup_concat (ID) intoStempchd fromTreeNodeswhereFind_in_set (PID,STEMPCHD) >0;END  while;RETURNStemp;ENDdelimiter;

Use the Find_in_set function directly with this getchildlst to find

select getChildLst(1);+-----------------+| getChildLst(1)  |+-----------------+| $,1,2,3,4,5,6,7 |+-----------------+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 |+----+----------+------+select * from treeNodes where FIND_IN_SET(id, getChildLst(3));+----+----------+------+| id | nodename | pid  |+----+----------+------+|  3 | C        |    1 ||  6 | F        |    3 ||  7 | G        |    6 |+----+----------+------+

Advantages: Simple, convenient, no recursive call hierarchy depth limit (max_sp_recursion_depth, maximum 255);

Cons: Limited length, although it is possible to enlarge RETURNS varchar (1000), but there is always the maximum limit.

MySQL recursive query