Mysql recursive query

Mysql recursive query

Definition: Parent-Child query: query all the following child node data according to the parent ID; child Parent query: query all the parent node data according to the child ID;

1 Creating a Table

DROP TABLE IF EXISTS' shop ';CREATE TABLE' Shop ' (' shop_id ')int( One) not '0'auto_increment, ' parent_id 'int( One)DEFAULT '0', ' name 'varchar(255)DEFAULT '0', PRIMARY KEY(' shop_id ')) ENGINE=InnoDB auto_increment= $ DEFAULTCHARSET=UTF8;

2 Initializing data:

INSERT  into' Shop 'VALUES('1','0','Honten';INSERT  into' Shop 'VALUES('2','1','Branch 1';INSERT  into' Shop 'VALUES('3','1','Branch 2');INSERT  into' Shop 'VALUES('4','1','Branch 3');INSERT  into' Shop 'VALUES('5','1','Branch 4');INSERT  into' Shop 'VALUES('6','1','Branch 5');INSERT  into' Shop 'VALUES('7','1','Branch 6');

3 downward recursion

Recursive queries are implemented using the Find_in_set () function and the Group_concat () function:

DROP FUNCTION IF EXISTSQuerychildrshop;CREATE FUNCTIONQuerychildrshop (shopidINT)RETURNS VARCHAR(4000)BEGINDECLAREStempVARCHAR(4000);DECLAREStempchdVARCHAR(4000);SETStemp='';SETStempchd= CAST(Shopid as CHAR); whileStempchd is  not NULL DoSETStemp=CONCAT (Stemp,',', stempchd);SELECTGroup_concat (shop_id) intoStempchd fromShopWHEREFind_in_set (PARENT_ID,STEMPCHD)>0;END  while;RETURNstemp;END;

4. Call Method:

Select  from where Find_in_set (Shop_id,querychildshop (#{shopid}))

Query says store information with parent node 1

5 upward recursion

DROP FUNCTION IF EXISTSQueryChildrenAreaInfo1;CREATE FUNCTIONQueryChildrenAreaInfo1 (AreaidINT)RETURNS VARCHAR(4000)BEGINDECLAREStempVARCHAR(4000);DECLAREStempchdVARCHAR(4000);SETStemp='$';SETStempchd= CAST(Areaid as CHAR);SETStemp=CONCAT (Stemp,',', stempchd);SELECTParentID intoStempchd fromT_areainfoWHEREId=Stempchd; whileStempchd<> 0 DoSETStemp=CONCAT (Stemp,',', stempchd);SELECTParentID intoStempchd fromT_areainfoWHEREId=Stempchd;END  while;RETURNstemp;END;

6. Call Mode:

Select  from where Find_in_set (Shop_id,querychildrenareainfo1 (#{shopid}))

Query all ancestor nodes of a node with ID "7":

Mysql recursive query