MySQL 5.7新特性| Json Column和Generated Column(下）

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的系统变量）时对其他业务带来影响。

以上就是MySQL 5.7新特性| Json Column和Generated Column(下）的内容，更多相关内容请关注Work网（www.php.cn）！