MySQL递归查询树状表的子节点、父节点具体实现

使用mysql函数获取子节点

CREATE FUNCTION `sf_getmenu_childids`(rootId INT) 
RETURNS varchar(1000) 
BEGIN
DECLARE sTemp VARCHAR(1000);
DECLARE sTempChd VARCHAR(1000);


  SET sTemp = '$';
  SET sTempChd =cast(rootId as CHAR);


  WHILE sTempChd is not null DO
SET sTemp = concat(sTemp,',',sTempChd);
SELECT group_concat(id) INTO sTempChd FROM menu where FIND_IN_SET(pid,sTempChd)>0;
END WHILE;
  RETURN sTemp;

END

调用: 

select sf_getmenu_childids(0);

select * from menu where find_in_set(id,sf_getmenu_childids(0));

使用mysql函数获取父节点

CREATE FUNCTION `sf_getmenu_parentids`(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(pid) INTO sParentTemp FROM menu where FIND_IN_SET(id,sParentTemp)>0; 
END WHILE; 
RETURN sParentList; 
END; 

调用: 

select sf_getmenu_parentids(7);

select * from menu where find_in_set(id,sf_getmenu_parentids(7));