Mysql怎么对json数据进行查询及修改
这篇文章主要介绍“Mysql怎么对json数据进行查询及修改”,在日常操作中,相信很多人在Mysql怎么对json数据进行查询及修改问题上存在疑惑,小编查阅了各式资料,整理出简单好用的操作方法,希望对大家解答”Mysql怎么对json数据进行查询及修改”的疑惑有所帮助!接下来,请跟着小编一起来学习吧!
对json数据进行查询及修改
使用 字段->'$.json属性' 进行查询条件
使用 json_extract 函数查询,json_extract(字段, "$.json属性")
根据json数组查询,用 JSON_CONTAINS(字段, JSON_OBJECT('json属性', "内容")) : [{}]查询这种形式的json数组
MySQL5.7以上支持JSON的操作,以及增加了JSON存储类型
一般数据库存储JSON类型的数据会用JSON类型或者TEXT类型
几个相关函数
示例
我这里没有创建json的字段格式,而是使用了text存储json 。
注意:用JSON类型的话1)JSON列存储的必须是JSON格式数据,否则会报错。2)JSON数据类型是没有默认值的。
插入json格式的数据到这一列中:
{"age": "28", "pwd": "lisi", "name": "李四"}
查询
select * from `offcn_off_main` where json_extract(json_field,"$.name") = '李四'
select * from `offcn_off_main` where json_field->'$.name' = '李四'
使用explain可以查看到无法使用索引。
所以需要修改:
mysql原生并不支持json列中的属性索引,但是我们可以通过mysql的虚拟列间接的为json中的某些属性创建索引,原理就是为json中的属性创建虚拟列,然后通过给虚拟列建立索引,从而间接的给属性创建了索引。
在MySQL 5.7中,支持两种Generated Column,即Virtual Generated Column和Stored Generated Column,前者只将Generated Column保存在数据字典中(表的元数据),并不会将这一列数据持久化到磁盘上;后者会将Generated Column持久化到磁盘上,而不是每次读取的时候计算所得。很明显,后者存放了可以通过已有数据计算而得的数据,需要更多的磁盘空间,与Virtual Column相比并没有优势----(其实我觉得还是有优势毕竟会少一些查询计算)
因此,MySQL 5.7中,不指定Generated Column的类型,默认是Virtual Column。
如果需要Stored Generated Golumn的话,可能在Virtual Generated Column上建立索引更加合适,一般情况下,都使用Virtual Generated Column,这也是MySQL默认的方式。
格式如下:
fieldname <type> [ GENERATED ALWAYS ] AS ( <expression> ) [ VIRTUAL|STORED ] [ UNIQUE [KEY] ] [ [PRIMARY] KEY ] [ NOT NULL ] [ COMMENT <text> ]
所以我这里:
ALTER TABLE 'off_main' `names_virtual` VARCHAR(20) GENERATED ALWAYS AS (`json_field` ->> '$.name') not null;
Note: 利用操作符-» 来引用JSON字段中的KEY。在本例中字段names_virtual为虚拟字段,我把它定义成不可以为空。在实际的工作中,一定要集合具体的情况来定。因为JSON本身是一种弱结构的数据对象。也就是说的它的结构不是固定不变的。
给虚拟字段增加索引:
CREATE INDEX `names` ON `off_main`(`names_virtual`);
注意如果虚拟字段并不是创建表是添加的,而是后面加的,增加索引时如果有的行中虚拟字段为null,但是又设置了它不能为null,那么索引无法创建成功,提示column can not be null.
增加索引后 explain看下即可看到用到了索引,并且虚拟字段的值会随着json字段的属性修改而自动变化。
来看看修改
update off_main set json_field = json_set(json_field,'$.phone', '132') WHERE id = 45 //同时修改多个UPDATE offcn_off_main set json_field = json_set(json_field,'$.name',456,'$.age','bbb') WHERE id = 45
json_set() 方法存在的则会覆盖,不存在的会添加。
删除
UPDATE offcn_off_main set json_field = json_remove(json_field,'$.pwd','$.phone') WHERE id = 45
插入
UPDATE offcn_off_main set json_field = json_insert(json_field,'$.pwd','111') WHERE id = 45
insert与update不同之处在于insert不存在的会增加,存在的不会覆盖
Mysql处理json数据
如果数据量小的话,将json数据直接复制到mysql的json字段中,如果数据过大可以通过java等后台形式对json数据解析,然后写入数据库中。
查询操作
select *,json->'$.features[0].geometry.rings' as rings from JSON;
从一张表读取一部分数据存入另一张表中(一条数据)
insert into DT_village(name, border) SELECT json->'$.features[0].attributes.CJQYMC',json->'$.features[0].geometry.rings'from JSON;
读取json数据并写入数据库(此时使用的是定义函数的形式来执行方法,可以定义便量)
#清空数据库TRUNCATE table DT_village; #定义存储过程delimiter //DROP PROCEDURE IF EXISTS insert_test_val;##num_limit 要插入数据的数量,rand_limit 最大随机的数值CREATE PROCEDURE insert_test_val() BEGIN DECLARE i int default 0; DECLARE a,b varchar(5000); WHILE i<10 do set a=CONCAT('$.features[',i,'].attributes.CJQYMC'); set b=CONCAT('$.features[',i,'].geometry.rings'); insert into DT_village(name, border) select #json->'$.features[0].attributes.CJQYMC',json->'$.features[0].geometry.rings' # (json->a),(json->b) json_extract(json,a),json_extract(json,b) from JSON; set i = i + 1; END WHILE; END// #调用存储过程call insert_test_val();
调用游标的方式获取jsosn数据中的一行,并执行插入操作
delimiter //drop procedure if exists StatisticStore;CREATE PROCEDURE StatisticStore() BEGIN #创建接收游标数据的变量 declare j json;#存储json数据 DECLARE i int default 0; #创建总数变量,记录执行次数,控制循环 DECLARE a,b,c varchar(5000);#定义json数组中的某个数据的键值 #创建结束标志变量 declare done int default false; #创建游标 declare cur cursor for select json from JSON where name = '1'; #指定游标循环结束时的返回值 declare continue HANDLER for not found set done = true; #设置初始值 set a=CONCAT('$.features[',i,'].attributes.XZQDM'); set b=CONCAT('$.features[',i,'].attributes.XZQMC'); set c=CONCAT('$.features[',i,']'); #打开游标 open cur; #开始循环游标里的数据 read_loop:loop #根据游标当前指向的一条数据 fetch cur into j; #判断游标的循环是否结束 if done then leave read_loop;#跳出游标循环 end if; #这里可以做任意你想做的操作 WHILE i<11 do insert into dt_border(xzq_code,name,border) select json_extract(j,a),json_extract(j,b),json_extract(j,c) from JSON; set i = i + 1; END WHILE; #结束游标循环 end loop; #关闭游标 close cur; #输出结果 select j,i; END;#调用存储过程call StatisticStore();
到此,关于“Mysql怎么对json数据进行查询及修改”的学习就结束了,希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习,快去试试吧!若想继续学习更多相关知识,请继续关注编程网网站,小编会继续努力为大家带来更多实用的文章!
免责声明:
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341