MySQL :: MySQL 8.4 Reference Manual :: 15.1.20.8 CREATE TABLE and Generated Columns

Documentation Home

version 8.4

8.0 current
5.7

8.0 Japanese

MySQL 8.4 Reference Manual / ... / CREATE TABLE and Generated Columns

15.1.20.8 CREATE TABLE 和生成的列

CREATE TABLE 支持指定生成的列。生成列的值来自于列定义中的表达式。

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

以下是一个简单的示例，展示了一个存储三角形边长的表格，在 sidea 和 sideb 列中存储边长，并计算 hypotenuse 在 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 表格的任何应用程序都可以访问 hypotenuse 值，而不需要指定计算它们的表达式。

生成列定义语法如下：

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属性。
自动递增列不能用作生成列定义中的基本列。
CREATE TABLE语句如果表达式计算导致截断或提供错误输入，终止执行并拒绝DDL操作。

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

如果生成列使用TIMESTAMP数据类型，那么设置explicit_defaults_for_timestamp将被忽略。在这种情况下，如果该变量禁用，NULL将不转换为CURRENT_TIMESTAMP。如果列也声明为NOT NULLNULL将被明确地拒绝，返回ER_BAD_NULL_ERROR。

Note

表达式评估使用评估时间的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;

避免编写表达式的一种方法是创建一个视图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;

PREV HOME UP NEXT