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
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;
-- ID为3的数据：
call proc_json_arr12(3)