使用mysql存储过程来遍历json数组的值

mysql存储过程来取出json类型中的值

mysql的版本要是5.7及以上才行，因为在5.7才支持json类型。

1. *技术不是很好，现在就是写一些存储过程，前段时间需要做一个权限的划分之类的功能。类似A类用户可以查看其下的B类用户工作进展。然后最初想的是直接一对多的映射，将A的用户表中的ID和其对应的B类用户的ID集合一 一对应，这样就能实现简单的一对多。因为mysql有set类型，就想使用set字段来将B类用户的ID存入，然后遍历出来，奈何找了很多教程和文档发现取不出来。后来想到既然是ID组合，能不能用数组来实现，然后json刚好有json数组和json对象类型两种，话不多说来试一下吧。
   DROP TABLE IF EXISTS json;
   CREATE TABLE json (
   ID int(11) NOT NULL,
   IDJSON json NULL COMMENT ‘人员ID,json’,
   PRIMARY KEY (ID) USING BTREE
   ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
   可以看到这张表是没有主键的 因为我们这张表就是来存储user表里的ID字段和ID字段组成的json数组的，目标就是遍历出ID字段对应的IDJSON字段中的值

向表里插入几条记录
INSERT INTO json VALUES (17, ‘[25, 31, 33, 37, 69, 71, 73, 75]’);
INSERT INTO json VALUES (21, ‘[25, 31, 33, 37, 39, 67, 69, 71, 73, 75]’);
INSERT INTO json VALUES (175, ‘[25, 31, 33, 37, 39, 67, 69, 71, 75]’);
可以看到IDJSON字段插入时是一个json数组形式字符串，如果不是json类型就会报错。
最终表里的数据是这样的：

然后我们开始写存储过程来遍历出IDJSON字段中的值

CREATE DEFINER=root@% PROCEDURE proc_json_array(
i_userid int(11)
)
BEGIN
declare i int default 0;
declare v_length int(11);-- 接收数组的长度
declare v_a varchar(100);-- 接收数组的角标的变量
select json_length(IDJSON) into v_length from json where ID=i_userid;
DROP temporary TABLE IF EXISTS tmp_t;
create temporary table if not exists tmp_t(-- 创建临时表保存查询的结果集中的需要的数据
USERID int(11)
);
while i<v_length
do
set v_a=concat("’$[",i,"]’");
set @sql=concat(‘insert into tmp_t(USERID) select IDJSON->’,v_a ,’ from json where ID=’,i_userid);
prepare s from @sql;
execute s;
set i=i+1;
end while;
select USERID from tmp_t;
END

可以看到存储过程需要输入一个int类型参数i_userid 就是表中的ID字段的值。
然后declare声明了一个int型的变量i,它是用来保存循环的次数，然后定义了v_length变量来保存ID字段对应的IDJOSN字段中json数组的长度，接收数组下角标的变量就不说了，然后创建一个只有一个字段USERID的临时表tmp_t.用来将我们遍历出来的值插入进临时表，然后遍历完成后再一次性查出来，这样就只有一个结果集，下面的循环体中从数组的为0 的角标开始取值，因为用到变量所以就想到concat构造动态sql每次取出一个值就执行一次插入临时表的操作，遍历完成然后再查临时表的数据字段即可。 执行存储过程，输入参数17，得到的结果集为，需要注意的是操作j’son类型时的注意点，set v_a=concat("’$[",i,"{]}^{\prime }");此处拼接出来的应{为}^{\prime }$[i]'这样的形式，可以使用转义字符，@sql的拼接也要注意单引号处的操作。

这里只是对单层的json数组进行遍历，当然如果是多个字段类型的json如[{“A”:“VAL”,“B”:“VAL”},{“A”:“VAL”,“B”:“VAL”}]时只需在创建临时表时多一个字段，然后在值时参考mysql文档中json字段的用法就能取出。本人在使用时只是用来满足用户一对多的关系，比如A类用户的user表ID主键为1，user表中的B类用户ID为7，8，9，10，11，12需要和ID为1的用户关联表明他们之间的从属关系，那么只需在json表新增条记录ID为1,IDJSON值为[7, 8, 9, 10, 11, 12]即可 然后执行上述存储过程，输入1，得到单列多条的7，8，9，10，11，12结果集，如果需要查看这些用户的信息可以在存储过程的end之前加如下语句
select user表字段1，user表字段2… from user where ID IN(select USERID from tmp_t);
这样在第二个结果集就得到了这些用户的信息。
result1

result2

mysql的版本要是5.7及以上才行，因为在5.7才支持json类型。