mysql存储过程中遍历json数据

2018-12-10 16:29:30  卢浮宫  版权声明：本文为站长原创文章，转载请写明出处

一、今天在开发中遇到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<vUserNoLen do
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