MySQL recursively query the child node of the tree table, the parent node is implemented
Introduction: mysql version 5.0.94, this version and higher-level versions (5.5, 6, etc.) are not yet supported Cyclic recursive query, compared with sqlserver and oracle, mysql is difficult to traverse the child nodes in the tree table.This program focuses on referring to the following information and wrote two sql stored procedures.The child node query is copied, and the parent node query is made by reverse thinking.
The table structure and table data are not publicized.The query table user_role, the primary key is id, and each record has a parentid field (corresponding to the parent node of the 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 parent node*/
/*Call: 1, select getParentList(6) id; 2, select * From user_role where FIND_IN_SET(id, getParentList (2));*/
When it's done, PM said not to make storage structure, check it a few times in java...The storage structure has many advantages, including speeding up query speed, improving security, etc., but it will increase the load of the database.Many articles suggest a combination of them.I personally feel that it is better to use less.
The table structure and table data are not publicized.The query table user_role, the primary key is id, and each record has a parentid field (corresponding to the parent node of the record.Of course, a parent node will naturally have more than one Child node)
The 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;
/*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 parent node*/
/*Call: 1, select getParentList(6) id; 2, select * From user_role where FIND_IN_SET(id, getParentList (2));*/
When it's done, PM said not to make storage structure, check it a few times in java...The storage structure has many advantages, including speeding up query speed, improving security, etc., but it will increase the load of the database.Many articles suggest a combination of them.I personally feel that it is better to use less.
Tags
Technical otaku
Sought technology together
0 Comments