MySql存储过程拆分JSON数组插入子表2、思路考处理方式及方式的优缺点3、最终解决方案

103 阅读 0 评论 68 点赞

我是靠谱客的博主失眠冥王星，这篇文章主要介绍MySql存储过程拆分JSON数组插入子表2、思路考处理方式及方式的优缺点3、最终解决方案，现在分享给大家，希望可以做个参考。

1、业务背景

由于之前的各种版本因素，将主子表数据是以json进行存储的，主表里面存储子表的json array,由于联合查询效率太低，故需要将其拆分成主子表，但是之前的业务数据必须要导入到新表，这里介绍一下整体处理的思路，以及具体的处理方式。

之前的数据如下图所示：

需要转换成的子表结构

复制代码

1
2
3
4
5
6
7
CREATE TABLE `powoms_ultimate`.`Untitled`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `parent_id` int NULL,
  `code` varchar(255) NULL,
  `name` varchar(255) NULL,
  PRIMARY KEY (`id`)
);

2、思路考处理方式及方式的优缺点

方式1：采用java代码读取解析

优点：好实现

缺点：接口只能使用一次，以后就用不上，放在项目中，感觉很别扭，浪费资源

方式2：mysql脚本解析

缺点：mysql 基础语法对于不定长的json数组的支持不是很到位，很难搞

优点：相对于方式1来说是减少java项目的无用代码和接口

尝试：使用mysql的help_topic将多列转换成一列数据，存入临时表进行解析，参考博客地址

方式3: 使用mysql 存储过程+游标遍历的形式进行解析

缺点：需要熟悉mysql 存储过程和游标的语法，上手难

优点：减少java项目无用的接口，可以使用循环遍历单行的数据，可更好处理数据

3、最终解决方案

综上，选择第三种作为实现方案（脚本如下）：

复制代码

DELIMITER $
CREATE PROCEDURE `json_del`()
BEGIN
	#Routine body goes here...
	declare id2 int; #主表id
	declare json_data2 json; 
	declare json_length int;#遍历时存储临时长度的变量 
	declare count int; #本次处理总数
	declare i int DEFAULT(0); #游标循环变量
	declare j int default(0); #json 循环变量
	
	
	declare getData cursor for select id, json_data from data_table;
	select count(*) into count from data_table;
	
	open getData;
	
	repeat 
		 # 开始解析逻辑
	   fetch getData into id2,json_data2;
		 set j = 0;
	   set json_length = JSON_LENGTH(json_data2);
		 
		 repeat
		    # 判断空
				if JSON_UNQUOTE(JSON_EXTRACT(json_data2,CONCAT('$[',j,'].code'))) is not null THEN
				   insert into data_son(parent_id,code,name)
				   values(id2,JSON_UNQUOTE(JSON_EXTRACT(json_data2,CONCAT('$[',j,'].code'))),JSON_UNQUOTE(JSON_EXTRACT(json_data2,CONCAT('$[',j,'].name'))));
			  end if;
		 set j:= j+1; 
		 until j >= json_length end repeat;
  set i:= i+1;
  until i >= count end repeat;
	#关闭游标
	close getData;
	
END$
DELIMITER ;

call json_del();

DROP PROCEDURE json_del;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
DELIMITER $
CREATE PROCEDURE `json_del`()
BEGIN
	#Routine body goes here...
	declare id2 int; #主表id
	declare json_data2 json; 
	declare json_length int;#遍历时存储临时长度的变量 
	declare count int; #本次处理总数
	declare i int DEFAULT(0); #游标循环变量
	declare j int default(0); #json 循环变量
	
	
	declare getData cursor for select id, json_data from data_table;
	select count(*) into count from data_table;
	
	open getData;
	
	repeat 
		 # 开始解析逻辑
	   fetch getData into id2,json_data2;
		 set j = 0;
	   set json_length = JSON_LENGTH(json_data2);
		 
		 repeat
		    # 判断空
				if JSON_UNQUOTE(JSON_EXTRACT(json_data2,CONCAT('$[',j,'].code'))) is not null THEN
				   insert into data_son(parent_id,code,name)
				   values(id2,JSON_UNQUOTE(JSON_EXTRACT(json_data2,CONCAT('$[',j,'].code'))),JSON_UNQUOTE(JSON_EXTRACT(json_data2,CONCAT('$[',j,'].name'))));
			  end if;
		 set j:= j+1; 
		 until j >= json_length end repeat;
  set i:= i+1;
  until i >= count end repeat;
	#关闭游标
	close getData;
	
END$
DELIMITER ;

call json_del();

DROP PROCEDURE json_del;