概述

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

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

函数

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

Name	Description
(deprecated 5.7.9)	Append data to JSON document
	Create JSON array
	Append data to JSON document
	Insert into JSON array
	Return value from JSON column after evaluating path; equivalent to JSON_EXTRACT().
	Whether JSON document contains specific object at path
	Whether JSON document contains any data at path
	Maximum depth of JSON document
	Return data from JSON document
	Return value from JSON column after evaluating path and unquoting the result; equivalent to JSON_UNQUOTE(JSON_EXTRACT()).
	Insert data into JSON document
	Array of keys from JSON document
	Number of elements in JSON document
(deprecated 5.7.22)	Merge JSON documents, preserving duplicate keys. Deprecated synonym for JSON_MERGE_PRESERVE()
	Merge JSON documents, replacing values of duplicate keys
	Merge JSON documents, preserving duplicate keys
	Create JSON object
	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.
	Quote JSON document
	Remove data from JSON document
	Replace values in JSON document
	Path to value within JSON document
	Insert data into JSON document
	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
	Type of JSON value
	Unquote JSON value
	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_replace：只替换已经存在的旧值

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

json_insert：插入新值，但不替换已经存在的旧值

：有相同的属性会覆盖前面的，例子见

：有相同的属性不会覆盖，会变成一个数组

修改对象

比如有这样一个需求，想把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转成的字符串

第二种参数是个变长数组，麻烦多了，见

修改数组

修改数组可以使用，

删除

要删除某个属性，可以通过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而已