记录一下今天将json数据转化为mysql表的历程
数据源长这样
其中jsonstr字段是json类型,需要获取其中ID为3的jsonstr的data对象,并转化成表的形式
如果用Java代码应该是好实现的,我今天用了存储过程来做
代码如下:
CREATE PROCEDURE proc_json_arr12(
i_userid int(11)
)
BEGIN
declare i int default 0;
declare v_length int(11);-- 接收对象的个数,作为循环的边界
declare v_a varchar(100);-- 接收数组的角标的变量
-- 获取对象的个数
select json_length(JSON_EXTRACT(jsonstr,'$.data')) into v_length from jsontb j where ID=i_userid;
DROP temporary TABLE IF EXISTS test1;
-- 建立临时表保存查询的结果集中的须要的数据
create temporary table if not exists test1(
keyid varchar(10),
title varchar(100),
level varchar(10),
classtype varchar(10),
handlestatus varchar(10),
`date` date,
handlesammary varchar(100),
content text,
handleunit varchar(100),
deadtime varchar(100),
color varchar(100)
);
while i<v_length
do
set v_a=concat("$[",i,"]");
set @sql=concat("insert into test1(keyid,title,level,classtype,handlestatus,`date`,handlesammary,content,handleunit,deadtime,color)
select
-- 遍历解析,并去掉双引号
replace(JSON_EXTRACT(JSON_EXTRACT(jsonstr,'$.data[*].keyid'),'",v_a,"'),'\"',''),
replace(JSON_EXTRACT(JSON_EXTRACT(jsonstr,'$.data[*].title '),'",v_a,"'),'\"',''),
replace(JSON_EXTRACT(JSON_EXTRACT(jsonstr,'$.data[*].level '),'",v_a,"'),'\"',''),
replace(JSON_EXTRACT(JSON_EXTRACT(jsonstr,'$.data[*].classtype'),'",v_a,"'),'\"',''),
replace(JSON_EXTRACT(JSON_EXTRACT(jsonstr,'$.data[*].handlestatus '),'",v_a,"'),'\"',''),
replace(JSON_EXTRACT(JSON_EXTRACT(jsonstr,'$.data[*].date'),'",v_a,"'),'\"',''),
replace(JSON_EXTRACT(JSON_EXTRACT(jsonstr,'$.data[*].handlesammary'),'",v_a,"'),'\"',''),
replace(JSON_EXTRACT(JSON_EXTRACT(jsonstr,'$.data[*].content'),'",v_a,"'),'\"',''),
replace(JSON_EXTRACT(JSON_EXTRACT(jsonstr,'$.data[*].handleunit'),'",v_a,"'),'\"',''),
replace(JSON_EXTRACT(JSON_EXTRACT(jsonstr,'$.data[*].deadtime'),'",v_a,"'),'\"',''),
replace(JSON_EXTRACT(JSON_EXTRACT(jsonstr,'$.data[*].color'),'",v_a,"'),'\"','')
from jsontb where ID=",i_userid);
prepare s from @sql;
execute s;
set i=i+1;
end while;
-- 插入目标表
insert into yuqing select * from test1;
end;
-- ID为3的数据:
call proc_json_arr12(3)
有不懂的可以指出来,或者有更好的办法可以讨论
至于为什么叫proc_json_arr12,因为我改了12次