You need to enable JavaScript to run this app. 如何编写MySQL查询实现逗号分隔字段求和并更新对应行数据
优惠活动
大模型
产品
解决方案
定价
更多

如何编写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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.19 07:15:05