小白学习MySQL - Generated Columns功能

碰巧看到MySQL有这种的语法"INTEGER GENERATED ALWAYS AS IDENTITY"，一知半解，了解一下。

官方文档介绍了这种Generated Columns列的情况，

https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html

我们通过实验，体验一下功能，创建测试表triangle，字段sidec是来自于saidea和saidb的平方和的平方根，插入三条数据，

[email protected] 17:50:  [test]> CREATE TABLE triangle (
    ->   sidea DOUBLE,
    ->   sideb DOUBLE,
    ->   sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb)));
Query OK, 0 rows affected (0.15 sec)


[email protected] 17:50:  [test]> INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

可以看到，插入的时候不需要指定sidec的值或者表达式，而是直接计算出来了，

[email protected] 17:50:  [test]> select * from triangle;
+-------+-------+--------------------+
| sidea | sideb | sidec              |
+-------+-------+--------------------+
|     1 |     1 | 1.4142135623730951 |
|     3 |     4 |                  5 |
|     6 |     8 |                 10 |
+-------+-------+--------------------+
3 rows in set (0.00 sec)

这种Generated Columns列语法如下，

col_name data_type [GENERATED ALWAYS] AS (expr)
  [VIRTUAL | STORED] [NOT NULL | NULL]
  [UNIQUE [KEY]] [[PRIMARY] KEY]
  [COMMENT 'string']

其中expr表示该列的值是用这个表达式计算出来的，上述例子中只写了关键字AS，此处带上GENERATED ALWAYS关键字可能就更清晰。

语法中的VIRTUAL和STORED关键字的作用是表示该列怎么存储，

(1) VIRTUAL：不存储列值，当用到该列时，会进行计算，不会占用任何的存储空间。InnoDB支持在VIRTUAL列上创建二级索引对象。

(2) STORED：插入或更新行时，会计算和存储该列的值，因此他需要占用存储空间，并且能创建索引。

默认值是VIRTUAL，因此如果未指定关键字，则是创建一个VIRTUAL列。

Generated Columns的表达式，需要遵从一些使用规则，

• Literals, deterministic built-in functions, and operators are permitted. A function is deterministic if, given the same data in tables, multiple invocations produce the same result, independently of the connected user. Examples of functions that are nondeterministic and fail this definition: CONNECTION_ID(), CURRENT_USER(), NOW().

• Stored functions and loadable functions are not permitted.

• Stored procedure and function parameters are not permitted.

• Variables (system variables, user-defined variables, and stored program local variables) are not permitted.

• Subqueries are not permitted.

• A generated column definition can refer to other generated columns, but only those occurring earlier in the table definition. A generated column definition can refer to any base (nongenerated) column in the table whether its definition occurs earlier or later.

• The AUTO_INCREMENT attribute cannot be used in a generated column definition.

• An AUTO_INCREMENT column cannot be used as a base column in a generated column definition.

• As of MySQL 5.7.10, if expression evaluation causes truncation or provides incorrect input to a function, the CREATE TABLE statement terminates with an error and the DDL operation is rejected.

如果表达式评估输入的数据类型和列定义的类型不一致，会根据通常的MySQL类型转换规则对声明的类型进行强制隐式转换。

最近碰到了一个相关的需求，表中有三个时间类型的字段，aTime、bTime和cTime，但这三个字段都可能为空，检索的时候需要一个不为空的时间字段，逻辑是，如果aTime为空，就看bTime是否为空，如果还为空，就看cTime，这里用到了ifnull函数，IFNULL(IFNULL(aTime, bTime), cTime)，可以实现这需求。但是每次都是现计算，此时就可以选择Generated Columns，

alter table test add column dTime datetime
generated always as (IFNULL(IFNULL(aTime, bTime), cTime));

如此一来，每次新增或更新数据的时候，dTime都会自动计算，检索的时候，直接用这个字段就可以了，而且能为这个字段创建索引了，就将这个字段当做一个普通的字段，通过一些技巧，提升检索效率。

如果熟悉Oracle的朋友，可能会联想到，这个不就是Oracle中虚拟列的功能？好的功能，不同的数据库可能都支持，我们从这些数据库的设计中，同样能汲取一些高超的设计，融入到我们日常的工作中。

小白学习MySQL

《小白学习MySQL - 增量统计SQL的需求 - 开窗函数的方案》

《小白学习MySQL - 统计的"投机取巧"》

《小白学习MySQL - 增量统计SQL的需求》

《小白学习MySQL - 你碰到过这种无法登陆的场景？》

《小白学习MySQL - 不同版本创建用户的些许区别》

《小白学习MySQL - 随机插入测试数据的工具》

《小白学习MySQL - varchar类型字段为什么经常定义成255？》

《小白学习MySQL - 变通创建索引的案例一则》

《小白学习MySQL - “投机取巧”统计表的记录数》

《小白学习MySQL - 一次慢SQL的定位》

《小白学习MySQL - TIMESTAMP类型字段非空和默认值属性的影响》

《小白学习MySQL - 聊聊数据备份的重要性》

《小白学习MySQL - InnoDB支持optimize table？》

《小白学习MySQL - table_open_cache的作用》

《小白学习MySQL - 表空间碎片整理方法》

《小白学习MySQL - 大小写敏感问题解惑》

《小白学习MySQL - only_full_group_by的校验规则》

《小白学习MySQL - max_allowed_packet》

《小白学习MySQL - mysqldump保证数据一致性的参数差异》

《小白学习MySQL - 查询会锁表？》

《小白学习MySQL - 索引键长度限制的问题》

《小白学习MySQL - MySQL会不会受到“高水位”的影响？》

《小白学习MySQL - 数据库软件和初始化安装》

《小白学习MySQL - 闲聊聊》

如果您认为这篇文章有些帮助，还请不吝点下文章末尾的"点赞"和"在看"，或者直接转发pyq，

近期更新的文章：

《再次了解max_allowed_packet》

《MySQL的安全解决方案》

《Oracle 21c对JSON支持功能增强》

《Oracle同英超联赛数据统计和展示的结合》

《翻译专业资格(水平)考试介绍》

近期的热文：

《"红警"游戏开源代码带给我们的震撼》

文章分类和索引：

《公众号1000篇文章分类和索引》