mysql命令行模式下创建函数

修改命令分隔符为 “$$”，防止执行中函数中的 “;” 符截断函数

delimiter $$

执行创建函数

CREATE DEFINER=`root`@`%` FUNCTION `huai_GETPARLST`(rootId VARCHAR(32)) RETURNS varchar(1000) CHARSET utf8
BEGIN
 DECLARE sTemp VARCHAR(1000);
 DECLARE sTempPar VARCHAR(1000);
  DECLARE namesss VARCHAR(1000); 
 DECLARE namek VARCHAR(1000); 
 SET sTemp = ''; 
 SET sTempPar = rootId; 
 SET namesss = '';
 SET namek = '';
 WHILE sTempPar is not null and sTempPar <> '' DO 
  IF sTemp != '' THEN
   SET sTemp = concat(sTempPar,',',sTemp);
   SELECT `name` INTO namek FROM flow_gathering_area where id = sTempPar;
   IF namek != '' THEN
   SET namesss = concat(namek,',',namesss);
   ELSE
   SET namesss = namesss;
   END IF;
  ELSE
   SET sTemp = sTempPar;
   SELECT `name` INTO namesss FROM flow_gathering_area where id = sTempPar;
  END IF;
  SELECT group_concat(parent_id) INTO sTempPar FROM flow_gathering_area where parent_id<>id and FIND_IN_SET(id,sTempPar)>0; 
 END WHILE; 
RETURN namesss; 
END$$

执行成功

恢复命令分隔符为 ";"

delimiter ;

————————————————

原文链接：https://blog.csdn.net/weixin_44188501/article/details/119100524