Mysql如何对json数据进行查询及修改

作者:youcijibi 时间:2024-01-14 06:29:56 

对json数据进行查询及修改

  • 使用 字段->'$.json属性' 进行查询条件

  • 使用 json_extract 函数查询,json_extract(字段, "$.json属性")

  • 根据json数组查询,用 JSON_CONTAINS(字段, JSON_OBJECT('json属性', "内容")) : [{}]查询这种形式的json数组

  • MySQL5.7以上支持JSON的操作,以及增加了JSON存储类型

  • 一般数据库存储JSON类型的数据会用JSON类型或者TEXT类型

几个相关函数

Mysql如何对json数据进行查询及修改

示例

Mysql如何对json数据进行查询及修改

我这里没有创建json的字段格式,而是使用了text存储json 。

注意:用JSON类型的话1)JSON列存储的必须是JSON格式数据,否则会报错。2)JSON数据类型是没有默认值的。

插入json格式的数据到这一列中:

{"age": "28", "pwd": "lisi", "name": "李四"}

查询

1、

select * from `offcn_off_main` where json_extract(json_field,"$.name") = '李四'

2、

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: 利用操作符-&raquo; 来引用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数据

1.如果数据量小的话,将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();

来源:https://blog.csdn.net/youcijibi/article/details/106498942

标签:Mysql,json,数据,查询
0
投稿

猜你喜欢

  • 简化版的vue-router实现思路详解

    2024-05-10 14:17:28
  • js求一组数中的最大数

    2008-04-10 12:00:00
  • 如何将 Access 的 Memo 型态字段汇入到 SQL2005 的 nvarchar 型态字段

    2008-12-26 18:13:00
  • 点球小游戏python脚本

    2022-07-17 23:28:03
  • javascript 自动转到命名锚记

    2024-04-29 13:44:46
  • Python实现的计数排序算法示例

    2022-07-27 15:03:55
  • Pandas常用的数据结构和常用的数据分析技术

    2021-02-10 05:22:35
  • Python 异常处理Ⅳ过程图解

    2023-06-28 16:05:53
  • MySQL两种表存储结构性能比较测试过程

    2007-12-09 12:45:00
  • 基于python实现模拟数据结构模型

    2022-11-12 23:44:01
  • Python2和Python3中print的用法示例总结

    2022-07-19 02:27:58
  • python在线编译器的简单原理及简单实现代码

    2022-01-06 18:40:49
  • Python Collatz序列实现过程解析

    2023-01-11 18:26:23
  • 使用Python导出Excel图表以及导出为图片的方法

    2021-02-28 09:59:21
  • Go语言基础数组用法及示例详解

    2024-04-26 17:33:52
  • php获取访问者IP地址汇总

    2023-11-14 12:14:06
  • 只用50行Python代码爬取网络美女高清图片

    2023-08-29 07:45:51
  • pydantic resolve解决嵌套数据结构生成痛点分析

    2022-06-05 02:16:36
  • Docker创建Mysql容器的简单步骤

    2024-01-28 20:20:40
  • 按钮的反馈

    2009-01-01 20:06:00
  • asp之家 网络编程 m.aspxhome.com