mysql的json特性的应用

原创
2018/08/10 09:51
阅读数 4.4K

概述

说实话,个人不是很喜欢这种特性,关系型数据库就应该有关系型数据库的样子,而不是为了留住用户而强加给他一些nosql的特性,而且还没有人家做的好,这样反而会造成一种四不像的感觉。sql和nosql是互补的,不是竞争关系。

那既然碰到了就学习下吧,我们从使用者的角度来看,json的特性是否能完成以前结构化数据的操作以及不足之处

函数

如下,是官方文档中所支持的JSON操作

Name

Description

JSON_APPEND() (deprecated 5.7.9)

Append data to JSON document

JSON_ARRAY()

Create JSON array

JSON_ARRAY_APPEND()

Append data to JSON document

JSON_ARRAY_INSERT()

Insert into JSON array

->

Return value from JSON column after evaluating path; equivalent to JSON_EXTRACT().

JSON_CONTAINS()

Whether JSON document contains specific object at path

JSON_CONTAINS_PATH()

Whether JSON document contains any data at path

JSON_DEPTH()

Maximum depth of JSON document

JSON_EXTRACT()

Return data from JSON document

->>

Return value from JSON column after evaluating path and unquoting the result; equivalent to JSON_UNQUOTE(JSON_EXTRACT()).

JSON_INSERT()

Insert data into JSON document

JSON_KEYS()

Array of keys from JSON document

JSON_LENGTH()

Number of elements in JSON document

JSON_MERGE() (deprecated 5.7.22)

Merge JSON documents, preserving duplicate keys. Deprecated synonym for JSON_MERGE_PRESERVE()

JSON_MERGE_PATCH()

Merge JSON documents, replacing values of duplicate keys

JSON_MERGE_PRESERVE()

Merge JSON documents, preserving duplicate keys

JSON_OBJECT()

Create JSON object

JSON_PRETTY()

Prints a JSON document in human-readable format, with each array element or object member printed on a new line, indented two spaces with respect to its parent.

JSON_QUOTE()

Quote JSON document

JSON_REMOVE()

Remove data from JSON document

JSON_REPLACE()

Replace values in JSON document

JSON_SEARCH()

Path to value within JSON document

JSON_SET()

Insert data into JSON document

JSON_STORAGE_SIZE()

Space used for storage of binary representation of a JSON document; for a JSON column, the space used when the document was inserted, prior to any partial updates

JSON_TYPE()

Type of JSON value

JSON_UNQUOTE()

Unquote JSON value

JSON_VALID()

Whether JSON value is valid

这里还需要加上一个下面的函数,功能是吧json的字符串转成对象,否则会真的当成字符串插入到mysql中,下面会碰到这个问题

CAST('{"a":"b"}' AS JSON

准备

创建表

CREATE TABLE `user` (
`uid` INT(11) NOT NULL AUTO_INCREMENT,
`info` JSON NULL DEFAULT NULL,
`a` VARCHAR(50) NULL DEFAULT NULL,
`b` VARCHAR(50) NULL DEFAULT NULL,
PRIMARY KEY (`uid`),
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB

如上,info字段是JSON类型

新增

insert into user(info) values('{"mail": "abc", "name": "tomab", "address": "e"}');

和普通的插入操作一样,只不过值是符合json规范的字符串,不规范插不进去

查找

SELECT uid,json_extract(info,'$.mail') AS 'mail',json_extract(info,'$.name') AS 'name' FROM USER;

这样查找出来的数据会带了双引号“”,很不爽,我们使用JSON_UNQUOTE函数处理下

SELECT uid,JSON_UNQUOTE(json_extract(info,'$.mail')) AS 'mail',json_extract(info,'$.name') AS 'name' FROM USER;

这样mail字段就没有双引号了

大家也能看出来,这种取数据的方式,充斥了大量的JSON_UNQUOTE,json_extract和美元符号,相比结构化,sql数据长了不少,而且必须要as成pojo里对应的属性,否则你的属性名称会变成类似json_extract(info,'$.name'),这样ORM框架就不能正确的设值了

mysql也意识到了,所以提供了->和->>符合来代替,代码稍微简洁点,但是as依然必不可少

SELECT uid,info->'$.address',info->'$.name' AS 'name' FROM USER;
SELECT uid,info->>'$.address',info->>'$.name' AS 'name' FROM USER;

模糊查找和排序

SELECT * FROM user where info->'$.address' = 'e' and info->'$.name' like '%o%' order by info->'$.name'

确实都支持,但是没有索引,数据量大时,效率不会很高

好在mysql提供了虚拟列的功能,可以把json的属性建立成虚拟列,然后在该列上加索引即可,这里强调一下,虚拟列和json的特性没关系,只是刚好能用上而已,而且虚拟列在json特性之前就有了

ALTER TABLE user ADD user_name varchar(128) GENERATED ALWAYS AS (json_extract(info,'$.name')) VIRTUAL;
ALTER TABLE user ADD INDEX index_name (a,b,user_name)

该列是可以通过desc user来看到的,然后加上索引即可,并且该列是可以和表的普通列建立联合索引的

MySQL 5.7中,支持两种Generated Column,即Virtual Generated Column和Stored Generated Column,前者只将Generated Column保存在数据字典中(表的元数据),并不会将这一列数据持久化到磁盘上;后者会将Generated Column持久化到磁盘上,而不是每次读取的时候计算所得。很明显,后者存放了可以通过已有数据计算而得的数据,需要更多的磁盘空间

可以看到,无论哪种虚拟列,都增加了额外的操作或者空间,效率比结构化的数据要低

修改

覆盖

update user set info = '{"mail": "abc", "name": "tomab", "address": "e"}';

这种修改,直接把内容当做一个字符串覆盖,简单粗暴

部分修改

这种操作首先必须要完全了解存储的json的格式,把他当做一个json对象而不是字符串

修改对象可以使用JSON_INSERT()JSON_REPLACE()JSON_SET()JSON_MERGE_PATCH()JSON_MERGE_PRESERVE()

json_replace:只替换已经存在的旧值

json_set:替换旧值,并插入不存在的新值,这是最常用的

json_insert:插入新值,但不替换已经存在的旧值

JSON_MERGE_PATCH():有相同的属性会覆盖前面的,例子见https://dev.mysql.com/doc/refman/5.7/en/json-modification-functions.html#function_json-merge-patch

JSON_MERGE_PRESERVE():有相同的属性不会覆盖,会变成一个数组

修改对象

比如有这样一个需求,想把mail的值变成一个对象,即类似如下:

{
"mail": {
"a": "b"
},
"name": "David",
"address": "Shangahai"
}

屁颠的使用

update user set info=JSON_SET(info,'$.mail','{"a":"b"}') where uid=5;

发现,mysql把参数当做字符串来处理了,结果如下:

{
"mail": "{\"a\":\"b\"}",
"name": "co",
"address": "e"
}

不是我们想要的,有两种方式可以完成

一:使用上面说的CAST函数

update user set info=JSON_SET(info,'$.mail',CAST('{"a":"b"}' AS JSON )) where uid=5;

二:使用JSON_OBJECT 函数

update user set info=JSON_SET(info,'$.mail',JSON_OBJECT('a','b')) where uid=5;

可以看到,显然第一钟比较爽,使用mybatis操作时,sql类似如下:

update user set info=json_set(info,'$.mail',CAST(#{a} AS JSON )) where id=1

参数即是pojo转成的字符串

第二种参数是个变长数组,麻烦多了,见https://dev.mysql.com/doc/refman/5.7/en/json-creation-functions.html#function_json-object

修改数组

修改数组可以使用JSON_ARRAY_APPEND()JSON_ARRAY_INSERT()

删除

要删除某个属性,可以通过JSON_REMOVE来操作即可

update user set info=JSON_REMOVE(info,'$.mailx') where uid=5;

其他

  • 有了虚拟列,似乎一张表只需包含一个int类型的id,一个json类型的content就行了,需要搜索和排序的通过建立虚拟列的方式,但是如果列比较多会增加mysql本身的维护成本

  • json的格式要固定,不能随意更改,因为代码是和格式强耦合的,如果变了那要大改,所以这就是mysql和mongodb这类nosql的一个区别,mysql并不是无模式的,对于一张表的那个json字段,模式其实也是固化在json里面而已

  • 对单体类修改,mybatis自动生成的类不能用,需要自己写sql,属性名称千万不能弄错,没有语法提示,错了不容易发现

  • 开发成本(写代码)的成本增加

  • 维护的成本增加,如果后面需要对json里面的另外一个字段进行模糊查找和排序,那么得增加虚拟列,重建索引,代码也要改,而如果是结构化的数据,只要一个DDL即可

  • json看似更灵活,其实非常不灵活,远不如结构的数据灵活

  • 通过update的语句,我们其实可以看出来部分修改的操作,比如JSON_SET,并不是实际只修改部分的数据,而是把全量的数据加载到内存,然后修改部分数据,在把修改后的全量的数据设值到mysql中,只不过mysql提供了函数让我们方便的操作json而已

展开阅读全文
打赏
0
3 收藏
分享
加载中
打赏
0 评论
3 收藏
0
分享
返回顶部
顶部