根据节点查询所有子节点CREATE DEFINER=`root`@`localhost` FUNCTION `getChildList`(rootId varchar(100)) RETURNS varchar(2000) CHARSET latin1BEGIN DECLARE str varchar(2000); DECLARE cid varchar(100); SET str = ‘$‘; SET cid = rootId; WHILE cid is not null DO SET str = concat(str, ‘,‘, cid); SELECT group_concat(id) INTO cid FROM tree where FIND_IN_SET(parent_id, cid) >0; END WHILE; RETURN str; ENDselect * from tree where FIND_IN_SET(Id,getChildList(‘feca4059-cb86-4dd2-8ef9-aee6c06c4517‘))

根据节点查询所有父节点CREATE DEFINER=`root`@`localhost` FUNCTION `getParentList`(rootId varchar(100)) RETURNS varchar(1000) CHARSET latin1BEGIN DECLARE fid varchar(100) default ‘‘; DECLARE str varchar(1000) default rootId; WHILE rootId is not null do SET fid =(SELECT parent_id FROM tree WHERE id = rootId); IF fid is not null THEN SET str = concat(str, ‘,‘, fid); SET rootId = fid; ELSE SET rootId = fid; END IF; END WHILE; return str; ENDselect * from tree where FIND_IN_SET(Id,getParentList(‘589bde6b-b432-4563-9101-8ec93161e398‘))