13.1.18.7 创建 table 和生成的列

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

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

以下简单示例显示了一个 table，该 table 存储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);

从 table 中选择将产生以下结果：

mysql> SELECT * FROM triangle;
+-------+-------+--------------------+
| sidea | sideb | sidec              |
+-------+-------+--------------------+
|     1 |     1 | 1.4142135623730951 |
|     3 |     4 |                  5 |
|     6 |     8 |                 10 |
+-------+-------+--------------------+

使用triangletable 的任何应用程序都可以访问斜边值，而不必指定计算它们的 table 达式。

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

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

AS (expr)table 示已生成列，并定义了用于计算列值的 table 达式。 AS之前可以是GENERATED ALWAYS，以使列的生成性质更加明确。稍后将讨论 table 达式中允许或禁止的构造。

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

VIRTUAL：不存储列值，但在任何BEFORE触发器之后立即读取行时会对其进行评估。虚拟列不占用任何存储空间。

InnoDB支持虚拟列上的二级索引。参见第 13.1.18.8 节“辅助索引和生成的列”。

STORED：插入或更新行时，将评估并存储列值。存储的列确实需要存储空间，并且可以构建索引。

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

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

可以赋予其他属性以指示该列是已索引还是可以为NULL或提供 Comments。

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

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

如果 table 达式求值的数据类型不同于声明的列类型，则根据通常的 MySQL 类型转换规则，对声明的类型进行隐式强制转换。参见第 12.2 节“table 达式评估中的类型转换”。

Note

如果 table 达式的任何部分取决于 SQL 模式，则 table 的不同用法可能会产生不同的结果，除非在所有用法中 SQL 模式都相同。

对于创建 table...喜欢，目标 table 保留从原始 table 生成的列信息。

对于创建 table...选择，目标 table 不保留有关 selected-fromtable 中的列是否是生成的列的信息。语句的SELECT部分不能为目标 table 中的生成列分配值。

允许按生成的列进行分区。参见Table Partitioning。

存储的生成列上的外键约束不能使用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。

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

虚拟生成的列可以用作简化和统一查询的方法。可以将复杂条件定义为生成的列，并从 table 上的多个查询中引用该条件，以确保所有条件都使用完全相同的条件。
存储的生成的列可用作复杂条件的物化缓存，这些条件需要快速计算。
生成的列可以模拟功能索引：使用生成的列定义功能 table 达式并为其编制索引。这对于处理无法直接索引的类型的列(例如JSON列)很有用。有关详细示例，请参见索引生成的列以提供 JSON 列索引。

对于存储的生成列，此方法的缺点是值被存储两次。一次作为生成的列的值，一次作为索引。

如果为生成的列构建索引，则优化器将识别与列定义匹配的查询 table 达式，并在查询执行期间适当地使用该列中的索引，即使查询未按名称直接引用该列。有关详细信息，请参见第 8.3.10 节，“优化使用生成的列索引”。

Example:

假设 tablet1包含first_name和last_name列，并且应用程序经常使用这样的 table 达式来构造全名：

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

避免写出 table 达式的一种方法是在t1上创建视图v1，这使应用程序无需使用 table 达式即可直接选择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;

首页

MySql 中文文档

13.1.18.7 创建 table 和生成的列