Mysql recursive query tree table of child nodes, parent node specific implementation _mysql

Introduction: mysql5.0.94, this version and the more advanced versions (5.5, 6, and so on) have not yet supported circular recursive queries, and MySQL is difficult to traverse in the middle layer of the tree table compared to SQL Server and Oracle. This procedure focuses on the following data, wrote two SQL stored procedures, child node query is copied, the parent node query is reversed thinking.

Table structure and table data is not publicized, query table User_role, PRIMARY key is ID, each record has parentid field (to should record the parent node, of course, a parent node will naturally have more than one child node)

Copy Code code 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 isn't 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 isn't 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 parent node * *
/* Call: 1, select Getparentlist (6) ID; 2. Select * from User_role where Find_in_set (ID, getparentlist (2));

Finished, PM said do not get storage structure, in Java to check more than a few times bar ... The storage structure has many advantages, including faster query speed, increased security, and so on, but it will increase the database load, many articles suggest that the use of a little bit better.