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

版本 9.0

8.4 当前
8.0
5.7

8.0 日语

MySQL 9.0 参考手册 / ... / 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 précédé pour rendre la nature générée de la colonne plus explicite. Les constructions autorisées ou interdites dans l'expression sont discutées plus tard.

VIRTUAL 或 STORED 关键字指示如何存储列值，这会对列使用产生影响。

虚拟: 列值不存储，而是当读取行时在任何 BEFORE 触发器之后立即计算。虚拟列不占用存储空间。
InnoDB 支持虚拟列的辅助索引。请参见第 15.1.20.9 节，“辅助索引和生成列”。
存储: 当插入或更新行时，列值会被计算并存储。存储列需要存储空间，可以被索引。

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

允许在一个表中混合使用 虚拟 和 存储 列。

可以指定其他属性来指示该列是否被索引或是否可以为 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;

上一个开始上级下一个