Implementation of the child node and parent node of the MySQL recursive query tree table

Introduction: MySQL 5.0.94, which is a later version (5.5, 6, and so on) does not support recursive queries. Compared with SQL Server and oracle, it is difficult for mysql to traverse child nodes in a tree table. This program focuses on the following materials and writes two SQL stored procedures. The subnode query is a copy, and the parent node query is reverse thinking.

The table structure and table data will not be disclosed. The queried table user_role has the id Primary Key and each record has the parentid field (for the parent node that should be recorded, of course, A parent node will naturally have more than one child node)
Copy codeThe Code is as follows:
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;
/* Obtain the subnode */
/* Call: 1. select getChildList (0) id; 2. select * 5 From 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;
/* Obtain the parent node */
/* Call: 1. select getParentList (6) id; 2. select * From user_role where FIND_IN_SET (id, getParentList (2 ));*/

After this is done, pm says no storage structure should be obtained. Check it several times in java... The storage structure has many advantages, including faster query speed and higher security, but it will increase the database load. Many articles suggest using it in combination. I personally think it would be better to use it less.