mysql脚本补充数据 json_extract json中有数组 数组动态索引

背景：

有两个表，一个bulletin_info，一个common_infile表

bulletin_info表：id、request_json；其中request_json是一个json串。

common_infile表：id、info_id、file_id、file_key;其中info_id是bulletin_info表的id,file_id、file_key是request_json中解析出的字段。

历史数据中file_key未落库，现将file_key补充完整。

思路：

（1）查出common_infile中file_key为空的数据

（2）根据（1）中common_infile查出的info_id查出bulletin_info对应的request_json数据

（3）解析出后再将查询结果赋值到common_infile中的file_key中

（4）所需要的json数据是在list中，所以需要将数据解析出来

SELECT JSON_EXTRACT(request_json,CONCAT('$.params.commonFileList[',i,'].fileId')) FROM bulletin_info WHERE id = 1;

说明：其中使用了JSON_EXTRACT，CONCAT方法，i是变量，0,1,2...，在取值时要循环取值。

SELECT SUBSTRING_INDEX(SUBSTRING(JSON_EXTRACT(request_json,CONCAT('$.params.commonFileList[',i,'].fileKey')),2),'"',1) FROM bulletin_info WHERE id = 1

说明：fileKey字段是String类型,使用substring方法去掉"" 。

实现：

DROP PROCEDURE IF EXISTS cii_update;
DELIMITER //
CREATE PROCEDURE cii_update()
BEGIN 
	DECLARE	info_id_cii INT;
	DECLARE	info_id_flag INT;
	DECLARE file_id_cii LONG;
	DECLARE id_cii LONG;
	
	DECLARE file_id_info VARCHAR(32);
	DECLARE	file_key_info VARCHAR(128);
	
	DECLARE flag INT DEFAULT 0;
	DECLARE i INT DEFAULT 0;
	
	DECLARE cii_list CURSOR FOR SELECT id, info_id,file_id FROM common_infile WHERE ufms_key IS NULL ;#使用游标
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1;#设置flag赋值为1的条件
	OPEN cii_list;#打开游标
	FETCH cii_list INTO id_cii, info_id_cii,file_id_cii;#将游标中的值赋值到id_cii等变量
	SET info_id_flag = info_id_cii;
	WHILE flag <> 1 DO
		SET file_id_info = (SELECT JSON_EXTRACT(request_json,CONCAT('$.params.commonFileList[',i,'].fileId')) FROM bulletin_info WHERE id = info_id_cii);
		SET file_key_info = (SELECT SUBSTRING_INDEX(SUBSTRING(JSON_EXTRACT(request_json,CONCAT('$.params.commonFileList[',i,'].fileKey')),2),'"',1) FROM bulletin_info WHERE id = info_id_cii);
		UPDATE common_infile SET ufms_key = file_key_info WHERE id = id_cii AND file_id = file_id_info AND info_id = info_id_cii;
		FETCH cii_list INTO id_cii, info_id_cii,file_id_cii;#将游标中的值赋值到id_cii等变量
		SET i = i + 1;
		IF info_id_flag != info_id_cii THEN 
		SET info_id_flag = info_id_cii;
		SET i = 0;
		END IF;
	END WHILE;
     CLOSE cii_list;
END
//
CALL cii_update();

参考：

https://www.jianshu.com/p/9a0b4c00f491

https://blog.csdn.net/helloxiaozhe/article/details/86571387

https://blog.csdn.net/ice166/article/details/78830905

https://blog.csdn.net/yangzjchn/article/details/82705565

https://blog.csdn.net/dream_dt/article/details/79152799

https://blog.csdn.net/ghostyusheng/article/details/84260831

https://www.jb51.net/article/164679.htm

https://www.cnblogs.com/baizhanshi/p/9284782.html

https://blog.csdn.net/xiexingshishu/article/details/50191039

https://blog.csdn.net/pjymyself/article/details/81668157