MySQL中如何添加计算列求JSON数组列的元素总和？

我有表T，其中列a存储[1,4,3,6]这类JSON数组，想要添加生成列b存储数组元素总和。执行以下SQL时触发语法错误：

ALTER TABLE `T` ADD `b` int AS 
        (SELECT SUM(t.c) FROM JSON_TABLE(a, '$[*]' COLUMNS (c INT PATH '$')) AS t) NULL;

错误信息：

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT SUM(t.c) FROM JSON_TABLE(a, '$[*]' COLUMNS (c INT PATH '$')) AS t) NULL' at line 1

问题原因 #

MySQL的生成列表达式不允许包含子查询，这是导致语法错误的直接原因。

解决方案 #

方案1：创建自定义函数实现生成列（MySQL 8.0+） #

先创建一个计算JSON数组元素总和的自定义函数，再用这个函数定义生成列：

DELIMITER //
CREATE FUNCTION json_array_sum(json_arr JSON) RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE total INT DEFAULT 0;
    DECLARE val INT;
    DECLARE cur CURSOR FOR SELECT c FROM JSON_TABLE(json_arr, '$[*]' COLUMNS (c INT PATH '$')) AS t;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET val = NULL;
    
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO val;
        IF val IS NULL THEN
            LEAVE read_loop;
        END IF;
        SET total = total + val;
    END LOOP;
    CLOSE cur;
    
    RETURN total;
END //
DELIMITER ;

然后添加生成列：

ALTER TABLE `T` ADD `b` INT AS (json_array_sum(a)) NULL;

方案2：直接查询时计算（无需生成列） #

如果不需要持久化b列，仅在查询时计算总和，可使用以下语句：

SELECT 
    a,
    (SELECT SUM(t.c) FROM JSON_TABLE(a, '$[*]' COLUMNS (c INT PATH '$')) AS t) AS b
FROM `T`;

方案3：一次性更新列（适合已有b列的场景） #

如果表中已经存在b列，需要一次性计算并更新所有行的b值（假设表有主键id）：

UPDATE `T` 
JOIN (
    SELECT 
        id,
        SUM(t.c) AS sum_val
    FROM `T`, JSON_TABLE(a, '$[*]' COLUMNS (c INT PATH '$')) AS t
    GROUP BY id
) AS sub ON `T`.id = sub.id
SET `T`.b = sub.sum_val;

内容的提问来源于stack exchange，提问作者user15633186