MySQL recursively queries the child node and parent node of the tree table _ MySQL

MySQL recursively queries the child node and parent node bitsCN.com of the 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.

Reference: http://blog.csdn.net/ACMAIN_CHM/article/details/4142971#comments

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)

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 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 DOIF (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 ));*/

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. BitsCN.com