如何编写MySQL查询实现逗号分隔字段求和并更新对应行数据
MySQL逗号分隔字段求和更新方案
需求说明
现有如下数据表:
id |total | setcolumn 1 | 5 | 2,3,4 2 | 1 | 1,3 3 | 3 | 2,1,4
其中setcolumn字段存储的是英文逗号分隔的数值集合,需要编写MySQL查询语句将total字段更新为对应行setcolumn字段内所有数值的总和,预期更新后的效果如下:
id | total| setcolumn 1 | 9 | 2,3,4 2 | 4 | 1,3 3 | 7 | 2,1,4
实现方案
方案1:兼容MySQL 5.7及以上版本
利用JSON函数拆分字符串求和,代码如下:
UPDATE 你的实际表名 t1 JOIN ( SELECT id, SUM( JSON_EXTRACT( CONCAT('["', REPLACE(setcolumn, ',', '","'), '"]'), CONCAT('$[', n, ']') ) + 0 ) AS sum_val FROM 你的实际表名 -- 数值序列要覆盖setcolumn字段的最大元素个数,示例最多3个元素所以写到2即可,元素更多可自行补充 JOIN (SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) nums ON n < JSON_LENGTH(CONCAT('["', REPLACE(setcolumn, ',', '","'), '"]')) GROUP BY id ) t2 ON t1.id = t2.id SET t1.total = t2.sum_val;
使用前将代码中你的实际表名替换为自己的表名即可。
方案2:MySQL 8.0及以上版本(递归CTE实现,更灵活)
如果数据库版本支持递归CTE,不用提前知道最大元素个数,代码如下:
WITH RECURSIVE split_cte AS ( SELECT id, setcolumn, SUBSTRING_INDEX(setcolumn, ',', 1) AS num, SUBSTRING(setcolumn, LENGTH(SUBSTRING_INDEX(setcolumn, ',', 1)) + 2) AS rest FROM 你的实际表名 UNION ALL SELECT id, setcolumn, SUBSTRING_INDEX(rest, ',', 1), SUBSTRING(rest, LENGTH(SUBSTRING_INDEX(rest, ',', 1)) + 2) FROM split_cte WHERE rest <> '' ) UPDATE 你的实际表名 t JOIN ( SELECT id, SUM(num + 0) AS sum_val FROM split_cte GROUP BY id ) s ON t.id = s.id SET t.total = s.sum_val;
注意:当前把多个数值用逗号存在同一个字段的设计不符合数据库第一范式,后续如果有频繁拆分、统计这类字段的需求,建议拆分为关联子表存储,性能和可维护性都会大幅提升。
内容的提问来源于stack exchange,提问作者Akram Elhaddad
相关产品推荐
相关产品推荐

