MySQL recursive query tree data

Create a new table:

CREATE TABLE ' tree ' (  ' id ' int (one) not NULL auto_increment COMMENT ' primary key ',  ' parent_id ' int (one) ' NOT null,  ' name ' varchar (ten) not NULL,  PRIMARY KEY (' id ')) engine=innodb  DEFAULT Charset=utf8;

　　

Create a new function (refer to https://jingyan.baidu.com/article/647f01158ee0da7f2148a80b.html):

Drop FUNCTION if EXISTS getchildlst; CREATE FUNCTION ' Getchildlst ' (Rootid INT)  RETURNS varchar (+)  BEGIN  DECLARE stemp varchar (+);  DECLARE stempchd VARCHAR (+);  SET stemp = ' ^ ';  SET stempchd =cast (Rootid as CHAR);  While Stempchd was not a null do  SET stemp = concat (stemp, ', ', stempchd);  SELECT Group_concat (ID) into stempchd from tree where Find_in_set (parent_id,stempchd) >0;  END while;  RETURN stemp;  END  ;

Fill in the data:

ID parent_id Name

1 0 Root nodes
2 level 11 nodes
3 level 11 nodes
4 level 11 nodes
5 Level 32 Nodes
6 Level 42 Nodes
7 Level 22 Nodes
8 Level 53 Nodes
9 Level 53 Nodes
10 Level 53 Nodes
11 Level 63 Nodes
12 Level 114 Nodes
13 Level 104 Nodes

Query ID 1 node's child node ID splicing string:select Getchildlst (1));

Results: ^,1,2,3,4,5,6,7,8,9,10,11,12,13

Query tree Data results:SELECT * from tree where Find_in_set (ID, Getchildlst (5));

Results:

ID parent_id Name

5 Level 32 Nodes

8 Level 53 Nodes
9 Level 53 Nodes
10 Level 53 Nodes
13 Level 104 Nodes

MySQL recursive query tree data