# 先设置 ,不然会报错
set global log_bin_trust_function_creators=TRUE;
# 创建函数
CREATE FUNCTION separator_str_len(str VARCHAR(225), separator_str CHAR(1)) RETURNS int(10)
BEGIN
RETURN (LENGTH(str) - LENGTH(REPLACE(str, separator_str, '')))+1;
end
CREATE FUNCTION separator_str(str VARCHAR(225), separator_str CHAR(1), idx int(10)) RETURNS varchar(50) CHARSET utf8
BEGIN
RETURN SUBSTRING_INDEX(SUBSTRING_INDEX(str, separator_str, idx), separator_str, -1);
END
CREATE FUNCTION separator_str_distinct(str VARCHAR(225), separator_str CHAR(1)) RETURNS varchar(225) CHARSET utf8
BEGIN
DECLARE size INT DEFAULT separator_str_len(str, separator_str);
DECLARE i INT DEFAULT 0;
DECLARE tmp VARCHAR(50);
DECLARE str_distinct VARCHAR(225) DEFAULT '';
WHILE i < size DO
SET i = i + 1;
SET tmp = separator_str(str, separator_str, i);
SET str_distinct = if(FIND_IN_SET(tmp, str_distinct) > 0, str_distinct, CONCAT(str_distinct, separator_str, tmp));
END WHILE;
RETURN RIGHT(str_distinct, CHAR_LENGTH(str_distinct) - 1);
end
# 调用函数,第一个参数为需要分割去重的字符串,第二个参数为分隔符
# 应用在表字段上
更新上述表中的str_field字段的值, 在原先基础上添加 2,3,4,5,结果为2,3,4,5
UPDATE tb SET str_field = (select separator_str_distinct(CONCAT('2,3,4,5,',str_field),',') str_field)