MySQL recursive query tree table of the child node the parent node concrete implementation

Summary: The es0EN5.0.94 version and the more advanced versions (5.5, 6, etc.) do not support recursive queries, and mysql has difficulty traversing child nodes in a tree table layer by layer compared to sqlserver and oracle. This procedure mainly refers to the following data, wrote two sql stored procedures, child node query is copied, the parent node query is inverse thinking.

Table structure and table data are not published, query table user_role, primary key is id, each record has parentid field (for the parent node that should record, of course, a parent node will naturally have more than one child node)

 
CREATE FUNCTION `getChildList`(rootId INT) 
RETURNS varchar(1000) 
BEGIN 
DECLARE sChildList VARCHAR(1000); 
DECLARE sChildTemp VARCHAR(1000); 
SET sChildTemp =cast(rootId as CHAR); 
WHILE sChildTemp is not null DO 
IF (sChildList is not null) THEN 
SET sChildList = concat(sChildList,',',sChildTemp); 
ELSE 
SET sChildList = concat(sChildTemp); 
END IF; 
SELECT group_concat(id) INTO sChildTemp FROM user_role where FIND_IN_SET(parentid,sChildTemp)>0; 
END WHILE; 
RETURN sChildList; 
END; 
/* Get child nodes */ 
/* call : 1 , select getChildList(0) id; 2 , select * 5From user_role where FIND_IN_SET(id, getChildList(2));*/ 


CREATE FUNCTION `getParentList`(rootId INT) 
RETURNS varchar(1000) 
BEGIN 
DECLARE sParentList varchar(1000); 
DECLARE sParentTemp varchar(1000); 
SET sParentTemp =cast(rootId as CHAR); 
WHILE sParentTemp is not null DO 
IF (sParentList is not null) THEN 
SET sParentList = concat(sParentTemp,',',sParentList); 
ELSE 
SET sParentList = concat(sParentTemp); 
END IF; 
SELECT group_concat(parentid) INTO sParentTemp FROM user_role where FIND_IN_SET(id,sParentTemp)>0; 
END WHILE; 
RETURN sParentList; 
END; 
/* Get the parent node */ 
/* call : 1 , select getParentList(6) id; 2 , select * From user_role where FIND_IN_SET(id, getParentList(2));*/ 

That's done, pm said don't do the storage structure, look it up in java a couple of times... Storage structure has many advantages, including faster query speed, improved security, etc., but will increase the database load, many articles suggest the use of a combination, individuals also think less will be better.