MySQL5.7新增两种字段类型:Json和Generated,Generated型的产生和Json的关系密不可分,如果没有Generated类型,Json类型在强大,生产中可能也无法使用,因为Json不支持索引,但是如果要查询Json里的数据,没有索引就是全表扫描,在执行效率上肯定是不能用于生产环境的,但是有了Generated类型就不同了,Generated类型简单地说是一个虚拟字段,值是不可更新的,值来源其他字段或者字段间计算或是转化而来的,这种类型是可以创建索引,利用Generated的特性,就可以间接的给Json类型中的key创建索引,解决Json不能创建索引的问题。简而言之, Generated类型的产生,为Json类型在索引方面的问题提供了支持。JSON的值包含单个值、数组、元组、标注的Json格式等几种格式。

客观地说,Json类型的出现,为研发提供了便利,提供了类似NoSQL的支持,但是走DBA运维的角度来看,也许是一个麻烦,这个麻烦和text等大字段的麻烦是一致的,json可能在实际的生产中会成为text字段的变种,望DBA在以后5.7的使用中密切关注这个类型。

一、创建带有JSON字段的表

1、直接创建一张测试带JSON字段的表

CREATE TABLE json_test (

id int(11) NOT NULL AUTO_INCREMENT,

group_name varchar(20) DEFAULT NULL,

user_info json DEFAULT NULL,

PRIMARY KEY (id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、在表中添加一个JSON类型的字段

ALTER TABLE json_test ADD COLUMN user_info JSON DEFAULT NULL;

3、插入数据

image_thumb2

二、JSON字段相关查询

1、查询全部结果

image_thumb4

2:JSON_EXTRACT查询

JSON_EXTRACT这个函数主要用作精确匹配,如查找user_info中age=21的记录或者查询name=’wangwei’的记录,或者name like ‘%wangwei%’模糊查询:

SELECT * FROM json_test WHERE JSON_EXTRACT(user_info,'$.age')=21;

image_thumb6

image_thumb8

在值为数组的时候,数组的起始位置值为0,同时,MySQL在5.5.9的版本增加了->,提供了等价于JSON_EXTRACT另外一种写:

SELECT * FROM json_test WHERE user_info->"$.age"=21;

image_thumb10

image_thumb12

3、JSON_SEARCH查询

JSON_SEARCH字符串查找函数,查找json所有key中值为字符串’wangwei’的记录,为精确匹配;加上%的话有点like的意思:

SELECT * FROM json_test WHERE JSON_SEARCH(user_info,'all','wangwei') IS NOT NULL;

SELECT * FROM json_test WHERE JSON_SEARCH(user_info,'all','wangwei%') IS NOT NULL;

SELECT * FROM json_test WHERE JSON_SEARCH(user_info,'all','%wangwei%') IS NOT NULL;

4、JSON_CONTAINS判断key是否包含指定的值

JSON_CONTAINS判断key是否包含指定的值,返回值为0或1或者是null,对应的情况为不包含、包含、和指定的key不存在集中情况:

image_thumb14


5、JSON_CONTAINS_PATH判断key在字段中是否存在

JSON_CONTAINS_PATH判断key在字段中是否存在,返回值为0和1,格式为JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...),如果为one的时候,代表path中只要有一个在就为1,反之为0;如果为all的话必须全部包含,返回为1,反之为0:

image_thumb16

6、JSON_LENGTH长度查询

JSON_LENGTH是用来计算JSON字段的长度:

image_thumb18

7:JSON_DEPTH层级查询

JSON_DEPTH这个主要是用来查询Json字段的层级,空的为1,所以正常情况下Json字段类型不为空,这个值的查询出的结果应该大于等于2:

image_thumb20

8:JSON_TYPE查询Json字段中key值的数据类型

JSON_TYPE主要用于查询Json字段类型中key值的数据类型,显示的类型按照分类如下:

Purely JSON types:

OBJECT: JSON objects;ARRAY: JSON arrays;BOOLEAN: The JSON true and false literals;NULL: The JSON null literal

Numeric types:

INTEGER: MySQL TINYINT, SMALLINT, MEDIUMINT and INT and BIGINT scalars;DOUBLE: MySQL DOUBLE FLOAT scalars;DECIMAL: MySQL DECIMAL and NUMERIC scalars

Temporal types:

DATETIME: MySQL DATETIME and TIMESTAMP scalars;DATE: MySQL DATE scalars;TIME: MySQL TIME scalars

String types:

STRING: MySQL utf8 character type scalars;CHAR, VARCHAR, TEXT, ENUM, and SET

Binary types:

BLOB: MySQL binary type scalars;BINARY, VARBINARY, BLOB;BIT: MySQL BIT scalars

All other types:

OPAQUE (raw bits)

image_thumb22

9、JSON_UNQUOTE去掉前后特殊字符

JSON_UNQUOTE主要是用来去掉前后的特殊字符,格式化值,目前支持一下几种字符:

clip_image0234_thumb1

image_thumb24

三、JSON字段相关写操作

1JSON_ARRAY_APPEND数组类的追加

这个用法主要是处理JSON字段中值数组型的数据,主要是追加值,不是标准数组类型的,这个函数会自动转化为数组形式,把之前的值作为数组的第一个元素然后进行追加,有批量追加方法:

mysql> SET @j = '["a", ["b", "c"], "d"]';
在字段中第二个数值的数组中追加一个元素1:
mysql> SELECT JSON_ARRAY_APPEND(@j, '$[1]', 1);
+----------------------------------+
| JSON_ARRAY_APPEND(@j, '$[1]', 1) |
+----------------------------------+
| ["a", ["b", "c", 1], "d"]         |
+----------------------------------+
在字段中第一个数值的数组中追加一个元素2,如果不是数组形式的单个值会改变其为数组并追加:
mysql> SELECT JSON_ARRAY_APPEND(@j, '$[0]', 2);
+----------------------------------+
| JSON_ARRAY_APPEND(@j, '$[0]', 2) |
+----------------------------------+
| [["a", 2], ["b", "c"], "d"]      |
+----------------------------------+
在字段中第二个数值的第一个数组中追加一个元素3,如果不是数组形式的单个值会改变其为数组并追加:
mysql> SELECT JSON_ARRAY_APPEND(@j, '$[1][0]', 3);
+-------------------------------------+
| JSON_ARRAY_APPEND(@j, '$[1][0]', 3) |
+-------------------------------------+
| ["a", [["b", 3], "c"], "d"]         |
+-------------------------------------+
在字段中key为”b”的value数组中追加一个元素”x”:
mysql> SET @j = '{"a": 1, "b": [2, 3], "c": 4}';
mysql> SELECT JSON_ARRAY_APPEND(@j, '$.b', 'x');
+------------------------------------+
| JSON_ARRAY_APPEND(@j, '$.b', 'x')  |
+------------------------------------+
| {"a": 1, "b": [2, 3, "x"], "c": 4} |
+------------------------------------+
在字段中key为”c”的value数组中追加一个元素”y”:
mysql> SELECT JSON_ARRAY_APPEND(@j, '$.c', 'y');
+--------------------------------------+
| JSON_ARRAY_APPEND(@j, '$.c', 'y')    |
+--------------------------------------+
| {"a": 1, "b": [2, 3], "c": [4, "y"]} |
+--------------------------------------+
如果JSON字段为标准的JSON类型,使用这个函数则会把其变化数组形式,并追加值:
mysql> SET @j = '{"a": 1}';
mysql> SELECT JSON_ARRAY_APPEND(@j, '$', 'z');
+---------------------------------+
| JSON_ARRAY_APPEND(@j, '$', 'z') |
+---------------------------------+
| [{"a": 1}, "z"]                    |
+---------------------------------+
如果JSON字段为标准的JSON类型,批量追加:
mysql> SELECT JSON_ARRAY_APPEND(@j, '$', 'z','$', 'w');
+---------------------------------+
| JSON_ARRAY_APPEND(@j, '$', 'z') |
+---------------------------------+
| [{"a": 1}, "z", "w"]              |
+---------------------------------+

2JSON_ARRAY_INSERT数组类的插入

这个用法主要是处理JSON字段中值数组型的数据,主要是插入,不是标准数组类型的,这个函数会自动转化为数组形式,把之前的值作为数组的第一个元素然后进行追加:

mysql> SET @j = '["a", {"b": [1, 2]}, [3, 4]]';
在数组第1个值后面插入字符’x’ 元素:
mysql> SELECT JSON_ARRAY_INSERT(@j, '$[1]', 'x');
+------------------------------------+
| JSON_ARRAY_INSERT(@j, '$[1]', 'x') |
+------------------------------------+
| ["a", "x", {"b": [1, 2]}, [3, 4]]  |
在数组第1个值前面插入字符’x’ 元素:
mysql> SELECT JSON_ARRAY_INSERT(@j, '$[0]', 'x');
+------------------------------------+
| JSON_ARRAY_INSERT(@j, '$[1]', 'x') |
+------------------------------------+
| ["x","a", {"b": [1, 2]}, [3, 4]]  |
+------------------------------------+
在数组第100个值后面插入字符’x’ 元素,如果超长,则在最后一个值后面插入:
mysql> SELECT JSON_ARRAY_INSERT(@j, '$[100]', 'x');
+--------------------------------------+
| JSON_ARRAY_INSERT(@j, '$[100]', 'x') |
+--------------------------------------+
| ["a", {"b": [1, 2]}, [3, 4], "x"]    |
+--------------------------------------+
在数组第1个值中key为’b’的value数组第1个值前面入字符元素’x’:
mysql> SELECT JSON_ARRAY_INSERT(@j, '$[1].b[0]', 'x');
+-----------------------------------------+
| JSON_ARRAY_INSERT(@j, '$[1].b[0]', 'x') |
+-----------------------------------------+
| ["a", {"b": ["x", 1, 2]}, [3, 4]]       |
+-----------------------------------------+
在数组第2个值后面数组第1个值后面入字符元素’y’:
mysql> SELECT JSON_ARRAY_INSERT(@j, '$[2][1]', 'y');
+---------------------------------------+
| JSON_ARRAY_INSERT(@j, '$[2][1]', 'y') |
+---------------------------------------+
| ["a", {"b": [1, 2]}, [3, "y", 4]]     |
+---------------------------------------+
数组元素的批量插入:
mysql> SELECT JSON_ARRAY_INSERT(@j, '$[0]', 'x', '$[2][1]', 'y');
+----------------------------------------------------+
| JSON_ARRAY_INSERT(@j, '$[0]', 'x', '$[2][1]', 'y') |
+----------------------------------------------------+
| ["x", "a", {"b": [1, 2]}, [3, 4]]                      |
+----------------------------------------------------+

3JSON_INSERT标准JSON值的插入

如果这个key已经存在,则不会去覆盖旧值,不存在的插入:

mysql> SET @j = '{ "a": 1, "b": [2, 3]}';
mysql> SELECT JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]');
+----------------------------------------------------+
| JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]') |
+----------------------------------------------------+
| {"a": 1, "b": [2, 3], "c": "[true, false]"}          |
+----------------------------------------------------+

4JSON_MERGE合并操作

JSON_MERGE这个主要用于合并:如果两个是数组的,则合并为一个数组;如果两个是JSON的,则合并为一个JSON,如果两个JSON有相同的key,则value合并为数组;如果是两个单值的,则合并为一个数组;如果是一个数组,一个是JSON,则合并为一个数组:

mysql> SELECT JSON_MERGE('[1, 2]', '[true, false]');
+---------------------------------------+
| JSON_MERGE('[1, 2]', '[true, false]') |
+---------------------------------------+
| [1, 2, true, false]                       |
+---------------------------------------+
mysql> SELECT JSON_MERGE('{"name": "x"}', '{"id": 47}');
+-------------------------------------------+
| JSON_MERGE('{"name": "x"}', '{"id": 47}') |
+-------------------------------------------+
| {"id": 47, "name": "x"}                       |
+-------------------------------------------+
mysql> SELECT JSON_MERGE('{"id": "x"}', '{"id": 47}');
+-------------------------------------------+
| JSON_MERGE('{"id": "x"}', '{"id": 47}') |
+-------------------------------------------+
| {"id": ["x",47]}                             |
+-------------------------------------------+
mysql> SELECT JSON_MERGE('1', 'true');
+-------------------------+
| JSON_MERGE('1', 'true') |
+-------------------------+
| [1, true]                  |
+-------------------------+
mysql> SELECT JSON_MERGE('[1, 2]', '{"id": 47}');
+------------------------------------+
| JSON_MERGE('[1, 2]', '{"id": 47}') |
+------------------------------------+
| [1, 2, {"id": 47}]                    |
+------------------------------------+

5JSON_REMOVE删除操作

JSON_REMOVE删除操作,这个比较简单,删除字段中某个值,数组的或是JSON格式的都可以:

mysql> SET @j = '["a", ["b", "c"], "d"]';
mysql> SELECT JSON_REMOVE(@j, '$[1]');
+-------------------------+
| JSON_REMOVE(@j, '$[1]') |
+-------------------------+
| ["a", "d"]                 |
+-------------------------+
mysql> SET @j = '{"name": "x","id": 47}';
mysql> SELECT JSON_REMOVE(@j, '$.id');
+-------------------------+
| JSON_REMOVE(@j, '$.id') |
+-------------------------+
| {"name": "x"}              |
+-------------------------+
mysql> SELECT JSON_REMOVE(@j, '$.id', '$.name');
+-----------------------------------+
| JSON_REMOVE(@j, '$.id', '$.name') |
+-----------------------------------+
| {}                                       |
+-----------------------------------+
mysql> SET @j = '{"name": "x","id": [47,48]}';
mysql> SELECT JSON_REMOVE(@j, '$.id[1]');
+-------------------------+
| JSON_REMOVE(@j, '$.id') |
+-------------------------+
| {"id": [47],"name": "x"} |
+-------------------------+

6JSON_REPLACE操作

JSON_REPLACE替换操作,有则替换,无则忽略。

mysql> SET @j = '{ "a": 1, "b": [2, 3]}';
mysql> SELECT JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]');
+-----------------------------------------------------+
| JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]') |
+-----------------------------------------------------+
| {"a": 10, "b": [2, 3]}                                    |
+-----------------------------------------------------+
mysql> SELECT JSON_REPLACE(@j, '$.a', 10, '$.b[1]',99);
+-----------------------------------------------------+
| JSON_REPLACE(@j, '$.a', 10, '$.b[1]',99') |
+-----------------------------------------------------+
| {"a": 10, "b": [2, 99]}                                    |
+-----------------------------------------------------+

7JSON_SET操作

JSON_SET操作为更新和插入操作,已经存在的去更新;不存在则插入,以下是JSON_SET、JSON_INSERT、JSON_REPLACE执行结果的区别:

image_thumb26

四、JSON字段索引以及Generated字段

JSON字段类型在当前的版本中自身没有索引,那么在生产中是非常可怕的,JSON字段的增、删、改、查效率可想而知,基本没法用,也许是基于此,MySQL5.7中提供了Generated字段类型,网上有叫生成列或是计算列的。这里先来了解一下什么是Generated Column。

1Generated Column介绍

Generated Column是MySQL 5.7.6引入的新特性,所谓Cenerated Column,就是数据库中这一列由其他列计算而得。引用官方参考手册中的例子予以说明:

CREATE TABLE triangle (
  sidea DOUBLE,
  sideb DOUBLE,
  sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb))
);
INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8);
mysql> SELECT * FROM triangle;
+-------+-------+--------------------+
| sidea | sideb | sidec              |
+-------+-------+--------------------+
|     1 |     1 | 1.4142135623730951 |
|     3 |     4 |                  5     |
|     6 |     8 |                 10     |
+-------+-------+--------------------+

Generated Column有两种,即Virtual Generated Column和Stored Generated Column,前者只将Generated Column保存在数据字典中(表的元数据),并不会将这一列数据持久化到磁盘上;后者会将Generated Column持久化到磁盘上,而不是每次读取的时候计算所得。很明显,后者存放了可以通过已有数据计算而得到的数据,需要更多的磁盘空间,与Virtual Column相比并没有优势,因此,MySQL 5.7中,不指定Generated Column的类型,默认是Virtual Column。虽然一般情况下都应该使用Virtal Generated Column,但是,目前使用Virtual Generated Column还有很多限制:不能用作主键、不能作为主键、不能创建全文索引和空间索引等,但是在后续的版本中可能支持,所以如果使用Generated Column字段做索引的话,还是使用Stored Generated Column吧,在使用Generated Column做索引上,JSON字段索引的解决方案,官方也是推荐使用Stored Generated Column。使用Stored Generated Column建表语句如下,只是在加了个单词:

CREATE TABLE triangle (
  sidea DOUBLE,
  sideb DOUBLE,
  sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb) STORED)
);

2Generated Column注意事项

Generated Column是不能进行写操作的,它是自动生成的;在创建的时候要考虑这个列的计算公式是否合理,不合理的话创建的时候不会报错,使用时插入值就会报错;Generated Column依赖的列在删除的时候会提示报错,必须先删除Generated Column才能再去删除它依赖的列;Generated Column定义不合法,如我们将generated column定义为 "x列+y列",很明显,x列或y列都是数值型,如果我们将x列或y列定义(或修改)为字符型,则预期会报错,但实际上我们可以正常创建,但是在插入的时候是会报错的。

image_thumb28

3、利用Generated ColumnJSON字段添加索引

正常情况下,JSON字段的相关查询是扫描全表的,因为JSON字段本身不能创建索引的,我们利用Generated Column特性,对JSON字段中相关key作为Generated Column来做生成列,然后对Generated Column做索引:

ALTER TABLE json_test ADD COLUMN age INT AS (JSON_EXTRACT(user_info,'$.age')) STORED, ADD KEY idx_age (age);

前后对比图如下:

image_thumb30

image_thumb32

可以很明显的看出,使用Generated Column并添加索引后,查询JSON字段中的值使用索引。

五、结束语

MySQL5.7中Generated Column和JSON Column的出现,使一些场景替代MongoDB等NoSQL提供了可能,虽然整体上还没有MongoDB等做的那么强大,但相信以后使用这两种类型的场景会越来越多, 同时对DBA的挑战也越来越大,希望密集使用JSON类型业务使用独立的MySQL实例来运行,以免JSON成为大字段(存储在JSON文档的大小JSON 列被限制为值 max_allowed_packet的系统变量)时对其他业务带来影响。