mysql的json特性的应用

最新推荐文章于 2024-08-06 09:00:00 发布

weixin_33958585

最新推荐文章于 2024-08-06 09:00:00 发布

阅读量126

点赞数

文章标签： json 数据库 java

原文链接：https://my.oschina.net/dxqr/blog/1925419

版权

为什么80%的码农都做不了架构师？>>>

概述

说实话，个人不是很喜欢这种特性，关系型数据库就应该有关系型数据库的样子，而不是为了留住用户而强加给他一些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而已