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<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

阅读更多
个人分类: 数据库
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

不良信息举报

mysql存储过程中遍历json数据

最多只允许输入30个字

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭