1. 首先考虑到数据库表中某字段存在多种分隔符,所以首先考虑将分割符统一
此处将其他符号(换行符,中文逗号,斜杠)统一为英文逗号为例
-- 中文逗号 -> 英文逗号
UPDATE `user` SET price=REPLACE(price,',', ',') WHERE user_id=1;
-- 换行符-> 英文逗号
UPDATE `user` SET price=REPLACE(price,'\n', ',') WHERE user_id=1;
-- 斜杠 -> 英文逗号
UPDATE `user` SET price=REPLACE(price,'/', ',') WHERE user_id=1;
2. 将字符串分割并累加
DROP FUNCTION IF EXISTS split_sum;
-- 定义一个函数,输入值为字符串类型,返回一个浮点型(使用DECIMAL)类型
CREATE FUNCTION split_sum (str VARCHAR(200)) RETURNS DECIMAL(10,3)
DETERMINISTIC
BEGIN
DECLARE max_size INT(11);
DECLARE i INT(11);
-- 10和3分别表示小数点左边和右边可以存储的十进制数字的最大个数
-- 例 1234,567,890.123
DECLARE sum DECIMAL(10,3);
SET max_size = LENGTH(str) - LENGTH(REPLACE(str, ',', '')) + 1;
SET i = 0;
SET sum = 0.000;
WHILE i < max_size DO
SET i = i + 1;
-- 使用while循环 获取每个分割完成的字符并累加
-- ROUND(X, D) 保留D位有效数字并返回X舍入到最接近的数字。
SET sum = sum + ROUND(SUBSTRING_INDEX(SUBSTRING_INDEX(str, ',', i),',',-1), 3);
END WHILE;
RETURN sum;
END;
UPDATE `user` set price_total=split_sum(price) WHERE user_id=1
-- price值: 100.1, 200.4, 300.5
-- price_total值: 601