mysql json 存储过程,mysql存储过程中遍历json数据

一、今天在开发中遇到mysql中遍历json数据的需求，查了些资料，然后特此记录，

二、在mysql5.7中是有一个方法用来取json数据的，JSON_EXTRACT(JSON.Val，key),详细代码如下

三、代码如下:

CREATE DEFINER=`root`@`localhost` PROCEDURE `fun_sys_user_local_mobileReg`(pUserId int,pIP varchar(95),params varchar(5000),out pRlt varchar(800))

BEGIN

-- BS用户移动版启用--本地存储过程

-- {"funName":"sys_user_mobileReg","uuid":"c39a37r98e06t40o49p30","localFunName":"sys_user_local_mobileReg","userNo":{"userNo0":"admin","userNo1":"test","userNo2":"hyc"},"userNoLen":3}

declare vUserNo,vUserNoTmp,vUserNoTmpKey varchar(200);

declare vUserNoLen int;

declare i int default 0;

set vUserNo = params->>'$.userNo';

set vUserNoLen = params->>'$.userNoLen';

while i

set vUserNoTmpKey = concat('$.userNo',i);

select JSON_EXTRACT(vUserNo, vUserNoTmpKey) into vUserNoTmp;

set vUserNoTmp = replace(vUserNoTmp,'\"','');

update user set mobileActive = 1 where userNo = vUserNoTmp;

set i = i+1;

end while;

set pRlt=concat('{"success":true,"msg":""}');

end

CREATE DEFINER=`jovie`@`%` PROCEDURE `fun_api_uploadEmpAttInfo`(pUserId int,pIP varchar(95),params varchar(5000),out pRlt varchar(800))

BEGIN

-- 考勤记录处理

-- {token:"4fb7edb9-8f21-4295-83ad-8552590deb0f",methodName:"uploadEmpAttInfo",clockNo:"006",

-- recData:[{"clockNo":"006","cardNo":"10066","cardTime":"2018-7-13 13:58:21"},{"clockNo":"006","cardNo":"10066","cardTime":"2018-7-13 13:58:23"}]}

declare vClockNo,vCardNo,vCardTime,vOriginal,vKeyTmp varchar(45);

declare vRecData,recTmp varchar(5000);

declare vEmpId,i,vRecLen int default 0;

declare vCardDate date;

set vRecData = params->>'$.recData';

set vRecLen = JSON_LENGTH(vRecData);

while i < vRecLen do

set vKeyTmp = concat('$[',i,']');

select JSON_EXTRACT(vRecData, vKeyTmp) into recTmp;

set vClockNo = JSON_EXTRACT(recTmp, '$.clockNo');

set vClockNo = replace(vClockNo,'\"','');

set vCardNo = JSON_EXTRACT(recTmp, '$.cardNo');

set vCardNo = replace(vCardNo,'\"','');

set vCardDate = JSON_EXTRACT(recTmp, '$.cardDate');

set vCardDate = replace(vCardDate,'\"','');

set vCardTime = JSON_EXTRACT(recTmp, '$.cardTime');

set vCardTime = replace(vCardTime,'\"','');

set vCardDate = cast(vCardTime as date);

set vOriginal = concat(vClockNo,'_',vCardNo,'_',vCardTime);

select empId into vEmpId from kqcard where cardNo = vCardNo;

set i = i + 1;

if not exists(select original from epcardoriginalrec where original = vOriginal) then

insert into epcardrec (clockNo,cardNo,empId,cardDate,cardTime,original)

values (vClockNo,vCardNo,vEmpId,vCardDate,vCardTime,vOriginal);

end if;

end while;

set pRlt= concat('{"success":true,"total":1,"msg":"考勤数据插入成功！"}');

end