一、今天在开发中遇到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":"-7-13 13:58:21"},{"clockNo":"006","cardNo":"10066","cardTime":"-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
也欢迎登录我的个人网站，里面有更多的文章及技术咨询在等你：
