创建表和生成的列
CREATE TABLE
支持生成列的规范。根据列定义中包含的表达式计算生成的列的值。
NDB
存储引擎还支持生成的列。
以下简单示例显示了一个表,该表存储sidea
和sideb
列中直角三角形的边的长度,并计算下斜边的长度sidec
(其他边的平方和的平方根):
CREATE TABLE triangle ( sidea DOUBLE, sideb DOUBLE, sidec DOUBLEAS (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
支持虚拟列上的二级索引。请参见“二级索引和生成的列”。STORED
:插入或更新行时,将评估并存储列值。存储的列确实需要存储空间,并且可以建立索引。
默认值是VIRTUAL
如果未指定任何关键字。
它允许混合VIRTUAL
和STORED
表中的列。
可以赋予其他属性以指示该列是已索引还是可以为NULL
,或提供注释。
生成的列表达式必须遵守以下规则。如果表达式包含不允许的构造,则会发生错误。
- 允许使用文字,确定性内置函数和运算符。如果给定表中的相同数据,则独立于所连接的用户,如果多次调用产生相同的结果,则该函数为确定性函数。的是不确定性和失败,这个定义函数的例子:
CONNECTION_ID()
,CURRENT_USER()
,NOW()
。 - 不允许使用存储的函数和用户定义的函数。
- 不允许存储过程和函数参数。
- 不允许使用变量(系统变量,用户定义的变量和存储的程序局部变量)。
- 不允许子查询。
- 生成的列定义可以引用其他生成的列,但只能引用表定义中较早出现的列。生成的列定义可以引用表中的任何基础(未生成)列,而不论其定义是更早还是更晚。
- 该
AUTO_INCREMENT
属性不能在生成的列定义中使用。 - 一个
AUTO_INCREMENT
列不能用作在生成的列定义的基柱。 - 如果表达式求值导致截断或向函数提供错误的输入,则该
CREATE TABLE
语句将以错误终止并拒绝DDL操作。
如果表达式求值的数据类型不同于声明的列类型,则根据通常的MySQL类型转换规则,对声明的类型进行隐式强制转换。请参见“表达式评估中的类型转换”。
注意表达式评估使用评估时有效的SQL模式。如果表达式的任何部分取决于SQL模式,则表的不同用法可能会产生不同的结果,除非在所有用法中SQL模式都相同。
对于CREATE TABLE ... LIKE
,目标表保留从原始表生成的列信息。
对于CREATE TABLE ... SELECT
,目标表不会保留有关selected-from表中的列是否是生成的列的信息。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列索引。对于存储的生成列,此方法的缺点是值存储两次。一次作为生成的列的值,一次作为索引。
- 如果为生成的列建立索引,则优化器将识别与列定义匹配的查询表达式,并在查询执行期间适当地使用该列的索引,即使查询未按名称直接引用该列也是如此。有关详细信息,请参见“优化器对生成的列索引的使用”。
例:
假设一个表t1
包含first_name
和last_name
列,并且应用程序经常使用这样的表达式来构造全名:
SELECT CONCAT(first_name,' ',last_name)AS full_nameFROM t1;
避免编写出表达的一种方法是创建一个视图v1
上t1
,其通过使它们简化的应用程序来选择full_name
的情况下直接使用的表达式:
CREATE VIEW v1AS SELECT *, CONCAT(first_name,' ',last_name)AS full_nameFROM t1;SELECT full_nameFROM 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_nameFROM t1;