13.1.20.8 CREATE TABLE and Generated Columns和生成的列

CREATE TABLE supports the specification of generated columns. CREATE TABLE支持指定生成的列。Values of a generated column are computed from an expression included in the column definition.生成的列的值是根据列定义中包含的表达式计算的。

Generated columns are also supported by the NDB storage engine.NDB存储引擎也支持生成的列。

The following simple example shows a table that stores the lengths of the sides of right triangles in the sidea and sideb columns, and computes the length of the hypotenuse in sidec (the square root of the sums of the squares of the other sides):下面的简单示例显示了一个表，该表存储了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);

Selecting from the table yields this result:从表中选择会产生以下结果：

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

Any application that uses the triangle table has access to the hypotenuse values without having to specify the expression that calculates them.任何使用triangle表的应用程序都可以访问斜边值，而无需指定计算斜边值的表达式。

Generated column definitions have this syntax:生成的列定义具有以下语法：

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

AS (expr) indicates that the column is generated and defines the expression used to compute column values. 指示已生成列并定义用于计算列值的表达式。AS may be preceded by GENERATED ALWAYS to make the generated nature of the column more explicit. AS前面可能会加上GENERATED ALWAYS，以使列的生成性质更加明确。Constructs that are permitted or prohibited in the expression are discussed later.表达式中允许或禁止的构造将在后面讨论。

The VIRTUAL or STORED keyword indicates how column values are stored, which has implications for column use:VIRTUAL或STORED关键字表示列值的存储方式，这对列的使用有影响：

The default is VIRTUAL if neither keyword is specified.如果两个关键字都未指定，则默认为VIRTUAL。

It is permitted to mix VIRTUAL and STORED columns within a table.允许在表中混合VIRTUAL列和STORED列。

Other attributes may be given to indicate whether the column is indexed or can be NULL, or provide a comment.可以提供其他属性来指示该列是否已编制索引或可以为NULL，或者提供注释。

Generated column expressions must adhere to the following rules. An error occurs if an expression contains disallowed constructs.生成的列表达式必须遵守以下规则。如果表达式包含不允许的构造，则会发生错误。

If the expression evaluates to a data type that differs from the declared column type, implicit coercion to the declared type occurs according to the usual MySQL type-conversion rules. 如果表达式的计算结果为与声明的列类型不同的数据类型，则会根据通常的MySQL类型转换规则对声明的类型进行隐式强制。See Section 12.3, “Type Conversion in Expression Evaluation”.请参阅第12.3节，“表达式计算中的类型转换”。

If a generated column uses the TIMESTAMP data type, the setting for explicit_defaults_for_timestamp is ignored. 如果生成的列使用TIMESTAMP数据类型，则忽略explicit_defaults_for_timestamp的设置。In such cases, if this variable is disabled then NULL is not converted to CURRENT_TIMESTAMP. 在这种情况下，如果禁用此变量，则NULL不会转换为CURRENT_TIMESTAMP。In MySQL 8.0.22 and later, if the column is also declared as NOT NULL, attempting to insert NULL is explicitly rejected with ER_BAD_NULL_ERROR.在MySQL 8.0.22及更高版本中，如果列也声明为NOT NULL，则尝试插入NULL会被显式拒绝，并出现ER_BAD_NULL_ERROR。

Note注意

Expression evaluation uses the SQL mode in effect at evaluation time. 表达式求值使用求值时有效的SQL模式。If any component of the expression depends on the SQL mode, different results may occur for different uses of the table unless the SQL mode is the same during all uses.如果表达式的任何组件依赖于SQL模式，则表的不同使用可能会产生不同的结果，除非SQL模式在所有使用过程中都是相同的。

For CREATE TABLE ... LIKE, the destination table preserves generated column information from the original table.对于CREATE TABLE ... LIKE，目标表保留从原始表生成的列信息。

For CREATE TABLE ... SELECT, the destination table does not preserve information about whether columns in the selected-from table are generated columns. 对于CREATE TABLE ... SELECT，目标表不会保留有关“从中选择”表中的列是否为生成列的信息。The SELECT part of the statement cannot assign values to generated columns in the destination table.语句的SELECT部分无法为目标表中生成的列赋值。

Partitioning by generated columns is permitted. 允许按生成的列进行分区。See Table Partitioning.请参阅表分区。

A foreign key constraint on a stored generated column cannot use CASCADE, SET NULL, or SET DEFAULT as ON UPDATE referential actions, nor can it use SET NULL or SET DEFAULT as ON DELETE referential actions.存储的生成列上的外键约束不能在ON UPDATE引用操作上使用CASCADE、SET NULL或SET DEFAULT，也不能在ON DELETE引用操作上使用SET NULL或SET DEFAULT。

A foreign key constraint on the base column of a stored generated column cannot use CASCADE, SET NULL, or SET DEFAULT as ON UPDATE or ON DELETE referential actions.存储的生成列的基列上的外键约束不能在ON UPDATE或ON DELETE引用操作时使用CASCADE、SET NULL或SET DEFAULT。

A foreign key constraint cannot reference a virtual generated column.外键约束无法引用虚拟生成的列。

Triggers cannot use NEW.col_name or use OLD.col_name to refer to generated columns.触发器不能使用NEW.col_name或使用OLD.col_name引用生成的列。

For INSERT, REPLACE, and UPDATE, if a generated column is inserted into, replaced, or updated explicitly, the only permitted value is DEFAULT.对于INSERT、REPLACE和UPDATE，如果生成的列被显式插入、替换或更新，则唯一允许的值为DEFAULT。

A generated column in a view is considered updatable because it is possible to assign to it. 视图中生成的列被认为是可更新的，因为它可以指定给它。However, if such a column is updated explicitly, the only permitted value is DEFAULT.但是，如果显式更新此列，则唯一允许的值为DEFAULT。

Generated columns have several use cases, such as these:生成的列有几个用例，例如：

Example:例子：

Suppose that a table t1 contains first_name and last_name columns and that applications frequently construct the full name using an expression like this:假设表t1包含first_name列和last_name列，并且应用程序经常使用以下表达式构造全名：

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

One way to avoid writing out the expression is to create a view v1 on t1, which simplifies applications by enabling them to select full_name directly without using an expression:避免写出表达式的一种方法是在t1上创建一个视图v1，它通过允许应用程序直接选择full_name而不使用表达式来简化应用程序：

CREATE VIEW v1 AS
SELECT *, CONCAT(first_name,' ',last_name) AS full_name FROM t1;

SELECT full_name FROM v1;

A generated column also enables applications to select full_name directly without the need to define a view:生成的列还允许应用程序直接选择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;