MySQL递归查询父节点或递归查询子节点-陈远波

DELIMITER $$

USE `yjlc_platform`$$
-- getCompanyParent 为函数名
DROP FUNCTION IF EXISTS `getCompanyParent`$$
-- getCompanyParent 为函数名 rootId为参数，可以自定义；初学者可以不用更改
CREATE DEFINER=`sysdbuser`@`%` FUNCTION `getCompanyParent`(rootId INT(36)) RETURNS VARCHAR(1000) CHARSET utf8
BEGIN
    DECLARE ptemp VARCHAR(1000);
    DECLARE ctemp VARCHAR(1000);
    SET ptemp = '#';
    SET ctemp = rootId;
    WHILE ctemp IS NOT NULL DO
      SET ptemp = CONCAT(ptemp, ',', ctemp);
      SELECT GROUP_CONCAT(id)
      -- id为主键id
      INTO ctemp
      -- company为查询的哪张表
      FROM company
      -- parent_id为该条数据的父id，根据自己定义的字段进行更改
      WHERE FIND_IN_SET(parent_id, ctemp) > 0;
    END WHILE;
    RETURN ptemp;
  END$$

DELIMITER ;


-- 根据函数查询父节点  6为要查询的哪个子节点id
SELECT * FROM company WHERE FIND_IN_SET(id, getCompanyParent(6));

DELIMITER $$

USE `yjlc_platform`$$
-- COMPANY_CHILD 函数名
DROP FUNCTION IF EXISTS `COMPANY_CHILD`$$
CREATE DEFINER=`sysdbuser`@`%` FUNCTION `COMPANY_CHILD`(rootId INT) RETURNS VARCHAR(1000) CHARSET utf8
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);
          -- id为主键id company为表名 - parent_id为父id
          SELECT GROUP_CONCAT(id) INTO sTempChd FROM `company` WHERE   FIND_IN_SET(parent_id,sTempChd)>0;
        END WHILE;
        RETURN SUBSTRING(sTemp,3);
    END$$
DELIMITER ;

-- 根据id查询子节点
SELECT * FROM company WHERE FIND_IN_SET(id, COMPANY_CHILD(6));