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

相关函数:

示例:

我这里没有创建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不存在的会增加,存在的不会覆盖