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

吾爱主题阅读：100 2024-04-02 18:54:44 评论：0

对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格式的数据到这一列中：

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

查询

1、

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

2、

1	`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默认的方式。

格式如下：

1	`fieldname <type> [ GENERATED ALWAYS ]` `AS` `( <expression> ) [ VIRTUAL\|STORED ] [` `UNIQUE` `[` `KEY` `] ] [ [` `PRIMARY` `]` `KEY` `] [` `NOT` `NULL` `] [ COMMENT <text> ]`

所以我这里：

1	`ALTER` `TABLE` `'off_main'` `names_virtual` `VARCHAR` `(20) GENERATED ALWAYS` `AS` (`json_field` ->> `'$.name'` `)` `not` `null` `;`

Note: 利用操作符-» 来引用JSON字段中的KEY。在本例中字段names_virtual为虚拟字段，我把它定义成不可以为空。在实际的工作中，一定要集合具体的情况来定。因为JSON本身是一种弱结构的数据对象。也就是说的它的结构不是固定不变的。

给虚拟字段增加索引：

1	`CREATE` `INDEX` `names` `ON` `off_main`(`names_virtual`);

注意如果虚拟字段并不是创建表是添加的，而是后面加的，增加索引时如果有的行中虚拟字段为null，但是又设置了它不能为null，那么索引无法创建成功，提示column can not be null.

增加索引后 explain看下即可看到用到了索引，并且虚拟字段的值会随着json字段的属性修改而自动变化。

来看看修改

1 2 3 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() 方法存在的则会覆盖，不存在的会添加。

删除

1	`UPDATE` `offcn_off_main` `set` `json_field = json_remove(json_field,` `'$.pwd'` `,` `'$.phone'` `)` `WHERE` `id = 45`

插入

1	`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数据解析，然后写入数据库中。

查询操作

1	`select` `*,json->` `'$.features[0].geometry.rings'` `as` `rings` `from` `JSON;`

从一张表读取一部分数据存入另一张表中（一条数据）

1 2 3 insert into DT_village( name , border) SELECT json-> '$.features[0].attributes.CJQYMC' ,json-> '$.features[0].geometry.rings' from JSON;

读取json数据并写入数据库(此时使用的是定义函数的形式来执行方法，可以定义便量)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 #清空数据库 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数据中的一行，并执行插入操作

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 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();