DELIMITER $$USE `topsale`$$DROP FUNCTION IF EXISTS `getShopIdByUserId`$$CREATE DEFINER=`root`@`%` FUNCTION `getShopIdByUserId`(userId VARCHAR())
RETURNS VARCHAR() CHARSET utf8 ##定义返回值类型 varchar()
BEGIN ##函数开始
DECLARE sTemp VARCHAR(); ##定义一个varchar类型的参数
DECLARE sTempChd VARCHAR(); ##定义一个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)>;
##根据父节点，查询出该父节点下的所有子节点的id，支持多级查询
END WHILE;
SET sTemp=CONCAT("'",SUBSTR(sTemp,),"'");
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(); 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)>; END WHILE;
SET sTemp=CONCAT("'",SUBSTR(sTemp,),"'");
SET sTemp = REPLACE(sTemp,",","','");
RETURN sTemp;
END$$DELIMITER ;
结果：
