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

Documentation Home

version 8.3

8.0 current
5.7

8.0 Japanese

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

15.1.20.8 创建表和生成列

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列。

其他属性可以指定列是否索引或可以为空，或者提供注释。

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

字面量、确定性内置函数和操作符是允许的。如果给定相同的表数据，多次调用将产生相同的结果，不管连接的用户是谁。例如，不确定性函数失败的定义：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。

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 在 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;

PREV HOME UP NEXT