MYSQL存储过程处理JSON中LIST数据

需求背景

项目中某个需求变更，导致JSON数据格式的字段中包含的LIST数据中的某个KEY值，需要批量从double类型变更string类型。
我这里使用SQL语句来处理该需求，当然也可以使用代码程序处理。

数据结构

需要把target_table表中的config字段中targetList中number的值由double类型变为string类型，向下取整

数据处理目标表结构
CREATE TABLE `target_table` (
  `id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `config` json DEFAULT NULL COMMENT '规则',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ';

config字段的数据结构
{
    "a": "a",
    "b": "b",
    "c": "c",
   ......
    "targetList": [
        {
            "time": "17:55",
            "unit": "片",
            "number": 1.0,
            "remark": "无"
        },
        {
            "time": "15:14",
            "unit": "片",
            "number": 2.0,
            "remark": "无"
        },
        {
            "time": "15:19",
            "unit": "片",
            "number": 3.0,
            "remark": "无"
        }
    ]
}

方案一

思路为计算总的查询条数为循环次数，然后取每一条数据的targetList的数据长度为内循环次数，针对targetList的每条数据逐条更新，但是通过默认排序和limit来控制数据一致性是不可靠的

CREATE DEFINER=`username`@`%` PROCEDURE `updateNumber`()
BEGIN
DECLARE x INT;
DECLARE y INT;
DECLARE idCondition VARCHAR(500);
DECLARE countNum INT;
DECLARE listSize INT;

SET countNum = (SELECT COUNT(*) FROM target_table);
SET x = 0;
SET y = 0;
WHILE x < countNum DO
		SET listSize = (SELECT JSON_LENGTH(JSON_EXTRACT(config, '$.targetList')) FROM target_table LIMIT x,1);
		SET idCondition = (SELECT id FROM target_table LIMIT x,1);
		WHILE y < listSize DO
			UPDATE target_table
			SET config = JSON_REPLACE(
			config,
			CONCAT( '$.targetList[',y,'].number' ),
			CAST(FLOOR(JSON_EXTRACT(config, CONCAT( '$.targetList[',y,'].number' ))) AS CHAR))
			WHERE id COLLATE utf8mb4_general_ci = idCondition;
			SET y = y + 1;
		END WHILE;
		SET y = 0;
	SET x = x + 1;
END WHILE;
END

方案二

根据JSON_REPLACE的特性，存在的key才会去更新，不存在的key则会跳过，所以根据最长数据长度来决定循环次数，通过每次循环更新全表数据targetList[x]来处理数据。
**缺点：**如果数据中有超长list，而大部分数据都为短list，则会浪费较多的时间。
**适用于：**每个list的长度都一样，或者差距不大的情况

CREATE DEFINER=`username`@`%` PROCEDURE `updateNumber2`()
BEGIN
DECLARE x INT;
SET x = 0;
WHILE x < 5 DO
	UPDATE target_table
	SET config = JSON_REPLACE(
	config,
	CONCAT( '$.targetList[',x,'].number' ),
	CAST(FLOOR(JSON_EXTRACT(config, CONCAT( '$.targetList[',x,'].number' ))) AS CHAR));
	SET x = x + 1;
END WHILE;
END

方案三

基于方案二的考虑，引入了游标进一步优化了该过程

CREATE DEFINER=`username`@`%` PROCEDURE `updateNumber3`()
BEGIN
  -- 1.定义变量, 变量声明必须在游标之前
	DECLARE i INT;
	DECLARE listSize INT;
	DECLARE done INT DEFAULT 0;
	DECLARE s_id VARCHAR(50);
  -- 2.定义游标，并将sql结果集赋值到游标中
	DECLARE cur CURSOR FOR (
		SELECT
			id 
		FROM
			target_table ) ;
		-- 可以接WHERE条件
		-- WHERE
		-- id = xxx) ;
  -- 3.定义HANDLER
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
	-- 4.定义异常状态，在执行过程中出任何异常设置result_code为0
  -- DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET result_code=0;
	
	-- 打开游标
	OPEN cur;
		-- 将游标中的值赋值给变量，注意：变量名不要和返回的列名同名，变量顺序要和sql结果列的顺序一致
		FETCH cur INTO s_id;
		WHILE done <> 1 DO
			-- 在这里可以根据需要进行业务操作
			SET i = 0;
			SET listSize = (SELECT JSON_LENGTH(JSON_EXTRACT(config, '$.targetList')) FROM target_table WHERE id COLLATE utf8mb4_general_ci = s_id);
			WHILE i < listSize DO
				UPDATE target_table
				SET config = JSON_REPLACE(
				config,
				CONCAT( '$.targetList[', i ,'].number' ),
				CAST(FLOOR(JSON_EXTRACT(config, CONCAT( '$.targetList[', i ,'].number' ))) AS CHAR))
				WHERE id COLLATE utf8mb4_general_ci = s_id;
				SET i = i + 1;
			END WHILE;
			FETCH cur INTO s_id;
		END WHILE;
	CLOSE cur;
END