1.添加字符串
CREATE DEFINER=`root`@`%` FUNCTION `addStrListItem`(`strList` varchar(10240),`addStr` varchar(32)) RETURNS varchar(10240) CHARSET utf8mb4
BEGIN
DECLARE tmpStr varchar(10240);-- 临时检查数据集
IF FIND_IN_SET(addStr,strList) = 0 THEN -- 如果不存在则添加
SET tmpStr = REPLACE( CONCAT_WS(',',strList,addStr), ',,' ,',');
ELSE
SET tmpStr = strList ;
END IF;
IF MID(tmpStr,1,1) = ',' THEN
SET tmpStr = MID(tmpStr,2, LENGTH(tmpStr) );
END IF ;
IF MID(tmpStr,-1,1) = ',' THEN
SET tmpStr = MID(tmpStr,1,LENGTH(tmpStr)-1) ;
END IF;
RETURN tmpStr;
END
2.删除字符串
CREATE DEFINER=`root`@`%` FUNCTION `rmStrListItem`(`strList` varchar(10240),`rmStr` varchar(32)) RETURNS varchar(10240) CHARSET utf8mb4
BEGIN
DECLARE tmpStr varchar(10240);-- 临时检查数据集
DECLARE iTry INT DEFAULT 0;
SET iTry = 100;
SET tmpStr = strList ;
WHILE FIND_IN_SET(rmStr,tmpStr) > 0 and iTry > 0 DO -- 如果存在,则删除,如果有多个重复则循环删除
SET tmpStr = REPLACE( CONCAT(',',tmpStr,','), CONCAT(',',rmStr,',') ,',');
SET tmpStr = REPLACE( tmpStr , ',,' , ',');
SET iTry = iTry - 1;
END WHILE;
IF MID(tmpStr,1,1) = ',' THEN
SET tmpStr = MID(tmpStr,2, LENGTH(tmpStr) );
END IF ;
IF MID(tmpStr,-1,1) = ',' THEN
SET tmpStr = MID(tmpStr,1,LENGTH(tmpStr)-1) ;
END IF;
RETURN tmpStr;
END