一文看懂mysql5.7特性JSON和generate column-数据存储的灵活性

内容导航：

一文看懂mysql5.7特性JSON和generate column-数据存储的灵活性

如果将json格式数据存储到类似mysql这样的关系型数据库中，怎么查

mysql5.7支持json是要干掉mongodb吗

mongodb和mysql5.7的json哪个更好，优缺点比较

一、一文看懂mysql5.7特性JSON和generate column-数据存储的灵活性

概述

今天主要介绍MySQL 5.7 的两个独有的功能，即 JSON 和 generate column。充分使用这两个功能，能够极大地提高数据存储的灵活性。

---

1、JSON

随着非结构化数据存储需求的持续增长，各种非结构化数据存储的数据库应运而生（如 MongoDB）。从最新的数据库使用 排行榜 来看， MongoDB 已经超过了 PostgreSQL，其火热程度可见一斑。

各大关系型数据库也不甘示弱，纷纷提供对 JSON 的支持，以应对非结构化数据库的挑战。 MySQL 数据库从 5.7.8 版本开始，也提供了对 JSON 的支持。其使用方式如下：

CREATE TABLE t1 (jdoc JSON); INSERT INTO t1 VALUES('{"key1": "value1", "key2": "value2"}');

MySQL 对支持 JSON 的做法是，在 server 层提供了一堆便于操作 JSON 的函数，至于存储，就是简单地将 JSON编码成 BLOB，然后交由存储引擎层进行处理，也就是说， MySQL 5.7 的 JSON 支持与存储引擎没有关系，

MyISAM 存储引擎也支持 JSON 格式。

MySQL 支持 JSON 以后，总是避免不了拿来与 MongoDB 进行一些比较。但是， MySQL 对 JSON 的支持，至少有两点能够完胜 MongoDB：

1.可以混合存储结构化数据和非结构化数据，同时拥有关系型数据库和非关系型数据库的优点 2.能够提供完整的事务支持

JSON值和非JSON值转换

转换规则为：

---

2、generate column

generated column 是 MySQL 5.7 引入的新特性，所谓 generated column，就是数据库中这一列由其他列计算而得。

例如，知道直角三角形的两条直角边，要求直角三角形的面积。很明显，面积可以通过两条直角边计算而得，那么，这时候就可以在数据库中只存放直角边，面积使用 generated column，如下所示：

CREATE TABLE triangle (sidea DOUBLE, sideb DOUBLE, area DOUBLE AS (sidea * sideb / 2)); insert into triangle(sidea, sideb) values(3, 4); select * from triangle;

在 MySQL 5.7 中，支持两种 generated column，即 virtual generated column 和 stored generated column

virtual generated column前者只将 generated column 保存在数据字典中（表的元数据），并不会将这一列数据持久化到磁盘上；stored generated column 会将 generated column 持久化到磁盘上，而不是每次读取的时候计算所得。很明显，后者存放了可以通过已有数据计算而得的数据，需要更多的磁盘空间，与 virtual column 相比并没有优势。

因此，在不指定 generatedcolumn 的类型时，默认是 virtual column，如下所示：

show create table triangle\G 

如果大家觉得 generate column 提供的功能，也可以在用户代码里面实现，并没有什么了不起的地方，那么,或许还有一个功能能够吸引你，就是为 generate column 创建索引。在这个例子中，如果需要根据面积创建索引以加快查询，就无法在用户代码里面实现，使用 generate column 就变得非常简单：

alter table triangle add index ix_area(area);

---

后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~

一、如果将json格式数据存储到类似mysql这样的关系型数据库中，怎么查

在MySQL与PostgreSQL的对比中，PG的JSON格式支持优势总是不断被拿来比较。其实早先MariaDB也有对非结构化的数据进行存储的方案，称为dynamic column，但是方案是通过BLOB类型的方式来存储。这样导致的问题是查询性能不高，不能有效建立索引，与一些文档数据库对比，优势并不大，故在社区的反应其实比较一般。当然，MariaDB的dynamic column功能还不仅限于非结构化数据的存储，但不在本文进行展开。
MySQL 5.7.7 labs版本开始InnoDB存储引擎已经原生支持JSON格式，该格式不是简单的BLOB类似的替换。原生的JSON格式支持有以下的优势：
JSON数据有效性检查：BLOB类型无法在数据库层做这样的约束性检查
查询性能的提升：查询不需要遍历所有字符串才能找到数据
支持索引：通过虚拟列的功能可以对JSON中的部分数据进行索引
首先我们来看如何在MySQL中使用原生的JSON格式：
mysql> create table user ( uid int auto_increment,
-> data json,primary key(uid))engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into user values (NULL,
-> '{"name":"David","mail":"","address":"Shangahai"}');
Query OK, 1 row affected (0.00 sec)

mysql> insert into user values (NULL,'{"name":"Amy","mail":""}');
Query OK, 1 row affected (0.00 sec)

可以看到我们新建了表user，并且将列data定义为了JSON类型。这意味着我们可以对插入的数据做JSON格式检查，确保其符合JSON格式的约束，如插入一条不合法的JSON数据会报如下错误：
mysql> insert into user values (NULL,"test");
ERROR 3130 (22032): Invalid JSON text: "Invalid value" at position 2 in value (or column) 'test'.

此外，正如前面所说的，MySQL 5.7提供了一系列函数来高效地处理JSON字符，而不是需要遍历所有字符来查找，这不得不说是对MariaDB dynamic column的巨大改进：
mysql> select jsn_extract(data, '$.name'),jsn_extract(data,'$.address') from user;
+-----------------------------+-------------------------------+
| jsn_extract(data, '$.name') | jsn_extract(data,'$.address') |
+-----------------------------+-------------------------------+
| "David" | "Shangahai" |
| "Amy" | NULL |
+-----------------------------+-------------------------------+
2 rows in set (0.00 sec)

当然，最令人的激动的功能应该是MySQL 5.7的虚拟列功能，通过传统的B+树索引即可实现对JSON格式部分属性的快速查询。使用方法是首先创建该虚拟列，然后在该虚拟列上创建索引：
mysql> ALTER TABLE user ADD user_name varchar(128)
-> GENERATED ALWAYS AS (jsn_extract(data,'$.name')) VIRTUAL;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> select user_name from user;
+-----------+
| user_name |
+-----------+
| "Amy" |
| "David" |
+-----------+
2 rows in set (0.00 sec)

mysql> alter table user add index idx_username (user_name);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0

然后可以通过添加的索引对用户名进行快速的查询，这和普通类型的列查询一样。而通过explain可以验证优化器已经选择了在虚拟列上创建的新索引：

mysql> explain select * from user where user_name='"Amy"'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user
partitions: NULL
type: ref
possible_keys: idx_username
key: idx_username
key_len: 131
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)

可以发现MySQL 5.7对于JSON格式堪称完美，相信PostgreSQL阵营需要寻找新的策略来“攻击”MySQL了吧。如无意外，还是会停留在优化器这块，毕竟这块是目前MySQL必须要克服的最大问题，好在MySQL团队已经在重构优化器代码，相信更好的优化器将会在下一个版本中全面爆发。而一大堆文档数据库们已经哭晕在厕所了吧。

二、mysql5.7支持json是要干掉mongodb吗

在MySQL与PostgreSQL的对比中，PG的JSON格式支持优势总是不断被拿来比较。其实早先MariaDB也有对非结构化的数据进行存储的方案，称为dynamic column，但是方案是通过BLOB类型的方式来存储。这样导致的问题是查询性能不高，不能有效建立索引，与一些文档数据库对比，优势并不大，故在社区的反应其实比较一般。当然，MariaDB的dynamic column功能还不仅限于非结构化数据的存储，但不在本文进行展开。
MySQL 5.7.7 labs版本开始InnoDB存储引擎已经原生支持JSON格式，该格式不是简单的BLOB类似的替换。原生的JSON格式支持有以下的优势：
JSON数据有效性检查：BLOB类型无法在数据库层做这样的约束性检查
查询性能的提升：查询不需要遍历所有字符串才能找到数据
支持索引：通过虚拟列的功能可以对JSON中的部分数据进行索引
mysql> create table user ( uid int auto_increment, -> data json,primary key(uid))engine=innodb; Query OK, 0 rows affected (0.01 sec) mysql> insert into user values (NULL, -> '{"name":"David","mail":"","address":"Shangahai"}'); Query OK, 1 row affected (0.00 sec) mysql> insert into user values (NULL,'{"name":"Amy","mail":""}'); Query OK, 1 row affected (0.00 sec)

可以看到我们新建了表user，并且将列data定义为了JSON类型。这意味着我们可以对插入的数据做JSON格式检查，确保其符合JSON格式的约束，如插入一条不合法的JSON数据会报如下错误：
mysql> insert into user values (NULL,"test"); ERROR 3130 (22032): Invalid JSON text: "Invalid value" at position 2 in value (or column) 'test'.

此外，正如前面所说的，MySQL 5.7提供了一系列函数来高效地处理JSON字符，而不是需要遍历所有字符来查找，这不得不说是对MariaDB dynamic column的巨大改进：
mysql> select jsn_extract(data, '$.name'),jsn_extract(data,'$.address') from user; +-----------------------------+-------------------------------+ | jsn_extract(data, '$.name') | jsn_extract(data,'$.address') | +-----------------------------+-------------------------------+ | "David" | "Shangahai" | | "Amy" | NULL | +-----------------------------+-------------------------------+ 2 rows in set (0.00 sec)

当然，最令人的激动的功能应该是MySQL 5.7的虚拟列功能，通过传统的B+树索引即可实现对JSON格式部分属性的快速查询。使用方法是首先创建该虚拟列，然后在该虚拟列上创建索引：
mysql> ALTER TABLE user ADD user_name varchar(128) -> GENERATED ALWAYS AS (jsn_extract(data,'$.name')) VIRTUAL; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select user_name from user; +-----------+ | user_name | +-----------+ | "Amy" | | "David" | +-----------+ 2 rows in set (0.00 sec) mysql> alter table user add index idx_username (user_name); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0

然后可以通过添加的索引对用户名进行快速的查询，这和普通类型的列查询一样。而通过explain可以验证优化器已经选择了在虚拟列上创建的新索引：
mysql> explain select * from user where user_name='"Amy"'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: user partitions: NULL type: ref possible_keys: idx_username key: idx_username key_len: 131 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec)

三、mongodb和mysql5.7的json哪个更好，优缺点比较

在MySQL与PostgreSQL的对比中，PG的JSON格式支持优势总是不断被拿来比较。其实早先MariaDB也有对非结构化的数据进行存储的方案，称为dynamic column，但是方案是通过BLOB类型的方式来存储。这样导致的问题是查询性能不高，不能有效建立索引