13.1.18.7 创建表和生成的列_MySQL 5.7 参考手册

MySQL 8.0 参考手册 / 第 13 章 SQL 语句 / 13.1 数据定义语句 / 13.1.20 CREATE TABLE 语句 / 13.1.18.7 创建表和生成的列

13.1.18.7 创建表和生成的列

CREATE TABLE支持生成列的规范。生成列的值是根据列定义中包含的表达式计算得出的。

NDB从 MySQL NDB Cluster 7.5.3 开始，存储引擎支持生成的列。

下面的简单示例显示了一个表，该表在 sidea和列中存储直角三角形的边长，并在（其他边的平方和的平方根）中 sideb计算斜边的长度：sidec

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 |
+-------+-------+--------------------+

任何使用该triangle表的应用程序都可以访问斜边值，而无需指定计算它们的表达式。

生成的列定义具有以下语法：

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

AS (expr) 指示列已生成并定义用于计算列值的表达式。AS 可以在前面GENERATED ALWAYS使列的生成性质更加明确。表达式中允许或禁止的结构将在后面讨论。

VIRTUALor关键字指示列值的STORED 存储方式，这对列的使用有影响：

VIRTUALBEFORE：不存储列值，但在任何触发器之后立即读取行时对其进行评估。虚拟列不占用存储空间。
InnoDB支持虚拟列上的二级索引。请参阅第 13.1.18.8 节，“二级索引和生成的列”。
STORED：在插入或更新行时评估和存储列值。存储列确实需要存储空间并且可以被索引。

VIRTUAL如果两个关键字都未指定，则为默认值。

允许在表中混合VIRTUAL和 STORED列。

可以给出其他属性以指示该列是否被索引或可以被索引NULL，或提供注释。

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

允许使用文字、确定性内置函数和运算符。一个函数是确定性的，如果给定表中的相同数据，多次调用产生相同的结果，独立于连接的用户。不确定且未通过此定义的函数示例：CONNECTION_ID(), CURRENT_USER(), NOW()。
不允许存储函数和可加载函数。
不允许使用存储过程和函数参数。
不允许使用变量（系统变量、用户定义的变量和存储的程序局部变量）。
不允许子查询。
生成的列定义可以引用其他生成的列，但只能引用那些在表定义中较早出现的列。生成的列定义可以引用表中的任何基本（非生成）列，无论其定义发生在更早还是更晚。
AUTO_INCREMENT不能在生成的列定义中使用该属性。
AUTO_INCREMENT列不能用作生成的列定义中的基列。
从 MySQL 5.7.10 开始，如果表达式求值导致截断或向函数提供不正确的输入， CREATE TABLE语句将以错误终止，并且 DDL 操作将被拒绝。

如果表达式的计算结果为不同于声明的列类型的数据类型，则根据通常的 MySQL 类型转换规则隐式强制转换为声明的类型。请参阅第 12.3 节，“表达式计算中的类型转换”。

笔记

如果表达式的任何部分依赖于 SQL 模式，则表的不同使用可能会产生不同的结果，除非 SQL 模式在所有使用过程中都相同。

对于CREATE TABLE ... LIKE，目标表保留从原始表生成的列信息。

对于CREATE TABLE ... SELECT，目标表不保留有关 selected-from 表中的列是否为生成列的信息。该 SELECT语句的一部分不能为目标表中的生成列赋值。

允许按生成的列进行分区。请参阅表分区。

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

存储生成列的基列上的外键约束不能使用CASCADE、 SET NULL、或SET DEFAULT asON UPDATE或ON DELETE 引用操作。

外键约束不能引用虚拟生成的列。

触发器不能使用或用于引用生成的列。 NEW.col_nameOLD.col_name

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

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

生成的列有几个用例，例如：

虚拟生成列可用作简化和统一查询的一种方式。一个复杂的条件可以定义为一个生成的列，并从表上的多个查询中引用，以确保它们都使用完全相同的条件。
存储的生成列可用作物化缓存，用于动态计算成本高昂的复杂条件。
生成列可以模拟函数索引：使用生成列定义函数表达式并对其进行索引。这对于处理无法直接索引的类型的列很有用，例如 JSON列；有关详细示例，请参阅索引生成的列以提供 JSON 列索引。
对于存储生成的列，这种方法的缺点是值被存储了两次；一次作为生成列的值，一次在索引中。
如果生成的列被索引，优化器会识别与列定义匹配的查询表达式，并在查询执行期间适当地使用列中的索引，即使查询不直接按名称引用列也是如此。有关详细信息，请参阅第 8.3.10 节，“生成列索引的优化器使用”。

例子：

假设一个表t1包含 first_name和last_name 列，并且应用程序经常使用如下表达式构造全名：

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

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

CREATE VIEW v1 AS
SELECT *, CONCAT(first_name,' ',last_name) AS full_name FROM t1;

SELECT full_name FROM v1;

生成的列还使应用程序能够 full_name直接选择而无需定义视图：

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;