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

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
  • 22
    点赞
  • 4
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

hyoka丶

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值