MySql 根据节点查询父节点或子节点

=`root`@`localhost` FUNCTION `getChildList`(rootId varchar(100)) RETURNS varchar(2000) CHARSET latin1 BEGIN 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; END select * 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 latin1
BEGIN   
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;  
END

select * from tree where FIND_IN_SET(Id,getParentList(‘589bde6b-b432-4563-9101-8ec93161e398‘))

 

MySql 根据节点查询父节点或子节点

标签：int   arch   mysq   arc   efault   default   find   mysql   pre