MySQL-获取一个节点的所有子节点或父节点

获取子节点：

   DELIMITER $$

DROP FUNCTION IF EXISTS `sose`.`getChildLst`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `getChildLst`(rootId varchar(50)) RETURNS varchar(1000) CHARSET utf8
BEGIN
       DECLARE sTemp VARCHAR(1000);
       DECLARE sTempChd VARCHAR(1000);
   
       SET sTemp = '$';
       SET sTempChd =rootId;
   
       WHILE sTempChd is not null DO
         SET sTemp = concat(sTemp,',',sTempChd);
         SELECT group_concat(node_id) INTO sTempChd FROM bus_dir where node_pid<>node_id and FIND_IN_SET(node_pid,sTempChd)>0;
       END WHILE;
       RETURN sTemp;
     END$$

DELIMITER ;

 

获取父节点：

    DELIMITER $$

DROP FUNCTION IF EXISTS `sose`.`getParentLst`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `getParentLst`(childId varchar(50)) RETURNS varchar(1000) CHARSET utf8
BEGIN
       DECLARE sTemp VARCHAR(1000);
       DECLARE sTempPar VARCHAR(1000);
   
       SET sTemp = '$';
       SET sTempPar =childId;
   
       WHILE sTempPar is not null DO
         SET sTemp = concat(sTemp,',',sTempPar);
         SELECT group_concat(node_pid) INTO sTempPar FROM bus_dir where node_pid<>node_id and FIND_IN_SET(node_id,sTempPar)>0;
       END WHILE;
       RETURN sTemp;
     END$$

DELIMITER ;
select * from bus_dir where FIND_IN_SET(node_id,getParentLst('A01|001|10|3|1')) order by node_id;//用这条语句来使用