MySQL Recursive query tree table

DROP TABLE IF EXISTS ' t_areainfo '; CREATE TABLE ' t_areainfo ' (' id ' int (one) not NULL auto_increment, ' level ' int (one) DEFAULT 0, ' name ' varchar (255), ' Parenti d ' Int (one), ' status ' int (one) default 0, PRIMARY KEY (' id ')) engine=innodb auto_increment=65 default Charset=utf8;insert in To ' T_areainfo ' values (' 1 ', ' 0 ', ' China ', ' 0 ', ' 0 '); insert INTO ' t_areainfo ' values (' 2 ', ' 0 ', ' North region ', ' 1 ', ' 0 '); INSERT into ' T_areainfo ' VALUES (' 3 ', ' 0 ', ' Southern Region ', ' 1 ', ' 0 '); insert INTO ' t_areainfo ' values (' 4 ', ' 0 ', ' Beijing ', ' 2 ', ' 0 '); insert INTO ' t_ Areainfo ' VALUES (' 5 ', ' 0 ', ' Haidian ', ' 4 ', ' 0 '); insert INTO ' t_areainfo ' values (' 6 ', ' 0 ', ' fengtai ', ' 4 ', ' 0 '); insert INTO ' T_ar Eainfo ' VALUES (' 7 ', ' 0 ', ' Chaoyang ', ' 4 ', ' 0 '); insert INTO ' t_areainfo ' values (' 8 ', ' 0 ', ' Beijing xx District 1 ', ' 4 ', ' 0 '); insert INTO ' t_a  Reainfo ' VALUES (' 9 ', ' 0 ', ' Beijing xx District 2 ', ' 4 ', ' 0 '); insert INTO ' t_areainfo ' values (' 10 ', ' 0 ', ' Beijing xx District 3 ', ' 4 ', ' 0 '); INSERT into ' T_areainfo ' values (' 11 ', ' 0 ', ' Beijing xx District 4 ', ' 4 ', ' 0 '); INSERT into ' t_areainfo ' values (' 12 ', ' 0 ', ' Beijing xx District 5 ', ' 4 ',' 0 '); insert INTO ' t_areainfo ' values (' 13 ', ' 0 ', ' Beijing xx District 6 ', ' 4 ', ' 0 '); insert INTO ' t_areainfo ' values (' 14 ', ' 0 ', ' xx District 7 ' in Beijing, ' 4 ', ' 0 '); insert INTO ' t_areainfo ' values (' 15 ', ' 0 ', ' Beijing xx District 8 ', ' 4 ', ' 0 '); insert INTO ' t_areainfo ' values (' 16 ', ' 0 ', ' Beijing XX District 9 ', ' 4 ', ' 0 '); insert INTO ' t_areainfo ' values (' 17 ', ' 0 ', ' xx district of Beijing ', ' 4 ', ' 0 '); insert INTO ' t_areainfo ' values (' 18 ', ' 0 ', ' Beijing xx District One ', ' 4 ', ' 0 '); insert INTO ' t_areainfo ' values (' 19 ', ' 0 ', ' xx district of Beijing ', ' 4 ', ' 0 '); insert INTO ' t_areainfo ' values  (' 20 ', ' 0 ', ' xx district of Beijing ', ' 4 ', ' 0 '); insert INTO ' T_areainfo ' VALUES (' 21 ', ' 0 ', ' Beijing xx region ', ' 4 ', ' 0 '); insert INTO ' t_areainfo ' VALUES (' 22 ', ' 0 ', ' xx district of Beijing ', ' 4 ', ' 0 '); insert INTO ' T_areainfo ' VALUES (' 23 ', ' 0 ', ' xx district of Beijing ', ' 4 ', ' 0 '); insert INTO ' t_a Reainfo ' VALUES (' 24 ', ' 0 ', ' Beijing xx District ', ' 4 ', ' 0 '); insert INTO ' t_areainfo ' values (' 25 ', ' 0 ', ' Beijing xx District ', ' 4 ', ' 0 '); Insert I NTO ' T_areainfo ' values (' 26 ', ' 0 ', ' Beijing xx District ', ' 4 ', ' 0 '); INSERT into ' t_areainfo ' values (' 27 ', ' 0 ', ' Beijing xx District 1 ', ' 4 ', ' 0 '); I Nsert into ' t_areainfo 'VALUES (' 28 ', ' 0 ', ' Beijing xx District 2 ', ' 4 ', ' 0 '); insert INTO ' T_areainfo ' VALUES (' 29 ', ' 0 ', ' Beijing xx District 3 ', ' 4 ', ' 0 '); insert INTO ' T_area Info ' VALUES (' 30 ', ' 0 ', ' Beijing xx District 4 ', ' 4 ', ' 0 '); insert INTO ' t_areainfo ' values (' 31 ', ' 0 ', ' Beijing xx District 5 ', ' 4 ', ' 0 '); INSERT INTO ' T_areainfo ' VALUES (' 32 ', ' 0 ', ' Beijing xx District 6 ', ' 4 ', ' 0 '); insert INTO ' t_areainfo ' values (' 33 ', ' 0 ', ' Beijing xx District 7 ', ' 4 ', ' 0 '); insert Into ' t_areainfo ' values (' 34 ', ' 0 ', ' Beijing xx District 8 ', ' 4 ', ' 0 '); INSERT into ' t_areainfo ' values (' 35 ', ' 0 ', ' Beijing xx District 9 ', ' 4 ', ' 0 '); I Nsert into ' t_areainfo ' values (' 36 ', ' 0 ', ' Beijing xx District ', ' 4 ', ' 0 '); INSERT into ' t_areainfo ' values (' 37 ', ' 0 ', ' xx district of Beijing 11 ', ' 4 ' , ' 0 '); insert INTO ' t_areainfo ' values (' 38 ', ' 0 ', ' Beijing xx District ', ' 4 ', ' 0 '); insert INTO ' t_areainfo ' values (' 39 ', ' 0 ', ' xx district of Beijing ' 4 ', ' 0 '); insert INTO ' t_areainfo ' values (' 40 ', ' 0 ', ' Beijing xx region ', ' 4 ', ' 0 '); insert INTO ' t_areainfo ' values (' 41 ', ' 0 ' , ' Beijing xx District, ' 4 ', ' 0 '); insert INTO ' t_areainfo ' values (' 42 ', ' 0 ', ' xx district of Beijing ', ' 4 ', ' 0 '); insert INTO ' t_areainfo ' values (' 43 ', ' 0 ', ' Beijing xx District 17 ', ' 4 ', ' 0 '); insert INTO ' t_areainfo ' values (' 44 ', ' 0 ', ' Beijing xx District ', ' 4 ', ' 0 '); insert INTO ' t_areainfo ' values (' 45 ', ' 0 ', ' xx district of Beijing + ', ' 4 ', ' 0 '); insert INTO ' t_areainfo ' values (' 1 ', ' 0 ', ' xx Province 1 ', ' 0 ', ' 47 '); INSERT INTO ' t_areainfo ' values (' 0 ', ' ', '  XX Province 2 ', ' 1 ', ' 0 '); insert INTO ' t_areainfo ' values (' x ', ' 0 ', ' xx Province 3 ', ' 1 ', ' 0 '); insert INTO ' t_areainfo ' values (' 49 ', ' 0 ', ' xx Province 4 ', ' 1 ', ' 0 '); insert INTO ' t_areainfo ' values (' + ', ' 0 ', ' xx Province 5 ', ' 1 ', ' 0 '); insert INTO ' t_areainfo ' values (' 51 ', ' 0 ', ' xx province 6 ', ' 1 ', ' 0 '); insert INTO ' t_areainfo ' values (' 0 ', ' xx Province 7 ', ' 1 ', ' 0 '); insert INTO ' t_areainfo ' values (' 53 ', ' 0 ', ' xx Province 8 ', ' 1 ', ' 0 '); insert INTO ' t_areainfo ' values (' si ', ' 0 ', ' xx Province 9 ', ' 1 ', ' 0 '); insert INTO ' t_areainfo ' values (' 55 ' , ' 0 ', ' xx province ', ' 1 ', ' 0 '); insert INTO ' t_areainfo ' values (' 1 ', ' 0 ', ' xx province one ', ' ', ' 0 '); insert INTO ' t_areainfo ' values ( ' 0 ', ' xx province ', ' 1 ', ' 0 '); insert INTO ' T_areainfo ' VALUES (' 0 ', ' xx province ', ' 1 ', ' 0 '); insert INTO ' t_areainfo ' VALU ES (' i ', ' 0 ', ' xx province 14 ',' 1 ', ' 0 '); insert INTO ' t_areainfo ' values (' x ', ' 0 ', ' xx province ', ' 1 ', ' 0 '); insert INTO ' t_areainfo ' values (' x ', ' 0 ', ' xx province 1 6 ', ' 1 ', ' 0 '); insert INTO ' t_areainfo ' values (' + ', ' 0 ', ' xx province ', ' 1 ', ' 0 '); insert INTO ' t_areainfo ' values (' 63 ', ' 0 ', ' xx province ', ' 1 ', ' 0 '); INSERT into ' T_areainfo ' VALUES (' + ', ' 0 ', ' xx province 19 ', ' 1 ', ' 0 ');-- Method one: Using FUNCTION to get all child nodes id--query incoming areaid and all of the following child nodes delimiter//drop FUNCTION IF EXISTS querychildrenareainfo; CREATE FUNCTION ' Querychildrenareainfo ' (areaid INT) RETURNS varchar (4000) Begindeclare stemp VARCHAR (4000);D Eclare STEMPCHD VARCHAR (4000); SET stemp = ' $ '; SET stempchd = cast (areaid as Char); While stempchd are not NULL doset stemp = CONCAT (stemp, ', ', stempchd); SELECT Group_concat (ID) into Stempchd from T_areainfo where Find_in_set (parentid,stempchd) >0; END While;return stemp; End//delimiter//2--Call Mode select Querychildrenareainfo (1); select * from T_areainfo where Find_in_set (ID, Querychildrenareainfo (20)); /*delimiter//create FUNCTION ' Querychildrenareainfo ' (Rootid inT) RETURNS varchar (begindeclare stemp varchar);D eclare stempchd varchar (1000); SET stemp = ' $ '; SET stempchd =cast (Rootid as CHAR); While stempchd are not null doset stemp = concat (stemp, ', ', stempchd); SELECT Group_concat (ID) into Stempchd from T_areainfo where Find_in_set (parentid,stempchd) >0; END while; RETURN stemp; end//delimiter; * *

MySQL recursive query tree table