MySQL :: MySQL 8.4 参考手册 :: 15.1.20.8 CREATE TABLE 和生成列

版本 8.4

8.0 日语

MySQL 8.4 参考手册 / ... / CREATE TABLE 和生成列

15.1.20.8 CREATE TABLE 和生成列

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

NDB 存储引擎也支持生成列。

以下简单示例显示了一个表，该表在 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，以使列的生成性质更加明确。表达式中允许或禁止的结构将在后面讨论。

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

VIRTUAL：列值不存储，而是在读取行时进行计算，紧接在任何 BEFORE 触发器之后。虚拟列不占用存储空间。
InnoDB 支持虚拟列上的二级索引。参见第 15.1.20.9 节，“二级索引和生成列”。
STORED：列值在插入或更新行时计算并存储。存储列确实需要存储空间，并且可以被索引。

如果未指定任何关键字，则默认为 VIRTUAL。

允许在一个表中混合使用 VIRTUAL 和 STORED 列。

可以提供其他属性来指示该列是否已索引或是否可以为 NULL，或者提供注释。

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

允许使用文字量、确定性内置函数和运算符。如果一个函数在表中的数据相同的情况下，多次调用会产生相同的结果，而与连接的用户无关，则该函数是确定性的。不符合此定义的非确定性函数示例：CONNECTION_ID()、CURRENT_USER()、NOW()。
不允许使用存储函数和可加载函数。
不允许使用存储过程和函数参数。
不允许使用变量（系统变量、用户定义的变量和存储程序局部变量）。
不允许使用子查询。
生成列定义可以引用其他生成列，但只能引用表定义中出现在其之前的生成列。生成列定义可以引用表中的任何基（非生成）列，无论其定义出现在之前还是之后。
在生成列定义中不能使用 AUTO_INCREMENT 属性。
在生成列定义中不能将 AUTO_INCREMENT 列用作基列。
如果表达式求值导致截断或向函数提供错误的输入，则 CREATE TABLE 语句将终止并报错，并且 DDL 操作将被拒绝。

如果表达式的计算结果为与声明的列类型不同的数据类型，则会根据通常的 MySQL 类型转换规则隐式强制转换为声明的类型。参见第 14.3 节，“表达式求值中的类型转换”。

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

注意

表达式求值使用求值时生效的 SQL 模式。如果表达式的任何组成部分依赖于 SQL 模式，则除非在所有使用过程中 SQL 模式都相同，否则在不同情况下使用该表可能会产生不同的结果。

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

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

允许按生成列进行分区。参见表分区。

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

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

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

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

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

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

生成列有多种用例，例如：

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

示例

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

SELECT CONCAT(first_name,' ',last_name) AS full_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;

生成列还使应用程序能够直接选择 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;