内容摘要：MySQL5.7新增两种字段类型：Json和Generated，Generated型的产生和Json的关系密不可分，如果没有Generated类型，Json类型在强大，生产中可能也无法使用，因为Jso

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、插入数据

二、JSON字段相关查询

1、查询全部结果

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; 

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

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

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不存在集中情况：

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：

6、JSON_LENGTH长度查询

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

7：JSON_DEPTH层级查询

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

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)

9、JSON_UNQUOTE去掉前后特殊字符

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

三、JSON字段相关写操作

1、JSON_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"]              | +---------------------------------+ 

2、JSON_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]]                      | +----------------------------------------------------+ 

3、JSON_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]"}          | +----------------------------------------------------+ 

4、JSON_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}]                    | +------------------------------------+ 

5、JSON_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"} | +-------------------------+ 

6、JSON_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]}                                    | +-----------------------------------------------------+ 

7、JSON_SET操作

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

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

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

1、Generated 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) ); 

2、Generated Column注意事项

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

3、利用Generated Column给JSON字段添加索引

正常情况下，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); 

前后对比图如下： 

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

五、结束语

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

【本文是专栏作者王伟的原创文章，转载请联系作者本人获取授权】

戳这里，看该作者更多好文