【真实用】将json数据转化为mysql表

不想做靓仔

已于 2023-04-21 15:30:55 修改

阅读量7.2k

点赞数 3

分类专栏： MySQL 其他文章标签： json mysql java

于 2022-07-18 20:09:37 首次发布

本文链接：https://blog.csdn.net/dz77dz/article/details/125858806

版权

其他同时被 2 个专栏收录

38 篇文章 4 订阅

订阅专栏

MySQL

12 篇文章 4 订阅

订阅专栏

"这篇博客记录了一位工程师如何使用MySQL存储过程将JSON数据转换为表格的过程。内容涉及创建临时表，遍历JSON数组，通过JSON_EXTRACT函数提取特定ID的"data"对象，并将结果插入临时表，最后将数据转移到目标表。存储过程中涉及到的步骤包括变量声明、循环处理和SQL动态构建。博客还提到了该过程的多次迭代改进。"

摘要由CSDN通过智能技术生成

记录一下今天将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)