生成列（Generated Column）

• MySQL 5.7引入了生成列（Generated Column），虚拟列是由数据库中的某一列由其他列计算而得。

 

• 生成列可以模拟函数索引：使用生成列定义函数表达式并对其进行索引。这对于处理无法直接索引的类型的列很有用，例如 JSON 列，而其中对于存储生成列（Stored Generated Column），这种方法的缺点是值被存储了两次；一次作为生成列的值，一次在索引中。因此默认情况下使用虚拟生成列（Virtual Generated Column）作为生成列进行索引

 

• 如果生成列被索引，优化器会识别与列定义匹配的查询表达式，并在查询执行期间适当地使用列中的索引，即使查询不直接按名称引用列也是如此。

 

• MySQL 5.7 中，支持两种生成列

 

• 存储生成列（Stored Generated Column）的外键约束不能使用 CASCADE、SET NULL 或 SET DEFAULT 作为 ON UPDATE 引用操作，也不能使用 SET NULL 或 SET DEFAULT 作为 ON DELETE 引用操作。

 

• 存储生成列（Stored Generated Column）的引用列上的外键约束不能使用 CASCADE、SET NULL 或 SET DEFAULT 作为 ON UPDATE 或 ON DELETE 引用操作。

 

• 存储生成列（Stored Generated Column）可用作物化缓存，用于动态计算成本高昂的复杂条件。

 

• 虚拟生成列（Virtual Generated Column）被外键约束引用。

 

• 虚拟生成列（Virtual Generated Column）可用作简化和统一查询的一种方式。一个复杂的条件可以定义为一个生成的列，并从表上的多个查询中引用，以确保它们都使用完全相同的条件。

 

• MySQL允许在表中混合 虚拟生成列（Virtual Generated Column） 和 存储生成列（Stored Generated Column）。

语法

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

 

 

• 如果生成列使用 TIMESTAMP 数据类型，则忽略 explicit_defaults_for_timestamp 的设置。在这种情况下，如果禁用此变量，则不会将 NULL 转换为 CURRENT_TIMESTAMP。在 MySQL 8.0.22 及更高版本中，如果该列也声明为 NOT NULL，则尝试插入 NULL 会被显式拒绝并返回 ER_BAD_NULL_ERROR。

 

• 生成的列表达式 (expr) 必须遵守以下规则。如果表达式包含不允许的构造，则会发生错误。

 

1. 生成的列表达式允许使用文字、确定性内置函数和运算符。如果给定表中相同的数据，多个调用产生相同的结果，而与连接的用户无关，则函数是确定的。但是不允许使用不确定且未通过此定义的函数，例如：CONNECTION_ID()、CURRENT_USER()、NOW()

 

2. 生成的列表达式不允许使用存储函数和可加载函数。

 

3. 生成的列表达式不允许使用存储过程和函数参数。

 

4. 生成的列表达式不允许使用变量（系统变量、用户定义的变量和存储的程序局部变量）。

 

5. 生成的列表达式不允许使用子查询。

 

6. 生成的列定义可以引用其他生成的列，但只能引用表定义中较早出现的列。生成的列定义可以引用表中的任何基列（非生成的），无论其定义发生在更早或更晚。

 

7. AUTO_INCREMENT 属性不能在生成的列定义中使用，且 AUTO_INCREMENT 列不能用作生成的列定义中的基列，因为自增的列是不确定的

 

• 生成的列表达式 (expr) 求值导致截断或为函数提供了不正确的输入，那么 CREATE TABLE 语句将终止并返回错误，DDL 操作将被拒绝。

 

• 生成列允许使用列名和别名表达式两种方法匹配

示例

CREATE TABLE t1 (

first_name VARCHAR(10),
last_name VARCHAR(10),
full_name VARCHAR(255) AS (CONCAT(first_name,' ',last_name))

);

 

SELECT full_name FROM t1;

 

SELECT CONCAT(first_name,' ',last_name) FROM t1;

 

• 避免写出表达式的一种方法是在 t1 上创建一个视图 v1，这通过使应用程序能够直接选择 full_name 而无需使用表达式来简化应用程序

示例

CREATE VIEW v1 AS

SELECT *, CONCAT(first_name,' ',last_name) AS full_name

FROM t1;

 

SELECT full_name FROM v1;

注

• 对于 INSERT、REPLACE 和 UPDATE，如果生成列被显式插入、替换或更新，则唯一允许的值为 DEFAULT。

 

• MySQL 中允许按生成列进行分区

 

• 触发器不能使用 NEW.col_name 或使用 OLD.col_name 来引用生成列。

 

• 视图中生成列被认为是可更新的，因为它可以分配给它。但是，如果显式更新此类列，则唯一允许的值为 DEFAULT。

 

 

 

来自 <https://dev.mysql.com/doc/refman/8.0/en/create-table-generated-columns.html>