mysql 函数获取子节点

DELIMITER $$

USE `topsale`$$

DROP FUNCTION IF EXISTS `getShopIdByUserId`$$

CREATE DEFINER=`root`@`%` FUNCTION `getShopIdByUserId`(userId VARCHAR(40)) 
RETURNS VARCHAR(1000) CHARSET utf8  ##定义返回值类型  varchar(4000)
BEGIN                    ##函数开始
    DECLARE sTemp VARCHAR(1000);    ##定义一个varchar类型的参数
    DECLARE sTempChd VARCHAR(1000);    ##定义一个varchar类型的参数
    
    SET sTemp = "";            ##给sTmp赋值
    SET sTempChd =CAST(userId AS CHAR);
    
    WHILE sTempChd IS NOT NULL DO   ##循环体
        SET sTemp = CONCAT(sTemp,",",sTempChd);  ##拼接sTemp
        
        SELECT GROUP_CONCAT(shop_id) INTO sTempChd FROM shop_saler_mapping  WHERE FIND_IN_SET(user_login_id,sTempChd)>0;  
        ##根据父节点，查询出该父节点下的所有子节点的id，支持多级查询
        END WHILE;
        SET sTemp=CONCAT("'",SUBSTR(sTemp,2),"'");
        SET sTemp = REPLACE(sTemp,",","','");
        RETURN sTemp;
    END$$

DELIMITER ;

结果：

DELIMITER $$

USE `topsale`$$

DROP FUNCTION IF EXISTS `func_getAllsubclass`$$

CREATE DEFINER=`root`@`%` FUNCTION `func_getAllsubclass`(partyId INT) RETURNS TEXT CHARSET utf8
BEGIN
    DECLARE sTemp TEXT;
        DECLARE sTempChd VARCHAR(1000);
        
        SET sTemp = "";
        SET sTempChd =CAST(partyId AS CHAR);
        
        WHILE sTempChd IS NOT NULL DO
        
        SET sTemp = CONCAT(sTemp,",",sTempChd);
        
        SELECT GROUP_CONCAT(party_id) INTO sTempChd FROM party WHERE FIND_IN_SET(parent_party_id,sTempChd)>0;
        
        END WHILE;
           SET sTemp=CONCAT("'",SUBSTR(sTemp,2),"'");
            SET sTemp = REPLACE(sTemp,",","','");
        RETURN sTemp;
    END$$

DELIMITER ;

结果：