mysql 生成行 mysql生成列

转载

信息流星 2023-11-02 12:39:21

文章标签 mysql 生成行数据库 mysql 生成列在列定义中包含表达式 文章分类 MySQL 数据库

文章目录

前言

如何创建生成列
生成列定义

VIRTUAL 或 STORED 关键字
生成列表达式必须遵守以下规则
生成列用例

alter 生成列

前言

索引（index） 在SQL中可以大大提高查询效率。
SQL索引列中，如果使用了运算和函数，索引将无法生效。
如果索引列的数据量大且稳定，可以使用 生成列 来固定对索引列的计算和函数运算，并对该生成列添加索引提高查询效率。

如何创建生成列

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

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

举例：

通过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支持虚拟列上的二级索引。
STORED：插入或更新行时，将评估并存储列值。存储的列确实需要存储空间，并且可以建立索引。
默认值是VIRTUAL如果未指定任何关键字。
它允许混合VIRTUAL和 STORED表中的列。
可以赋予其他属性以指示该列是索引还是可以为NULL，或提供注释。

生成列表达式必须遵守以下规则

如果表达式包含不允许的构造，则会发生错误：

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

如果表达式求值的数据类型不同于声明的列类型，则根据通常的MySQL类型转换规则，对声明的类型进行隐式强制转换。

注意：
如果表达式的任何部分取决于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_nameOLD.col_name
对于INSERT， REPLACE和 UPDATE，如果将生成列显式插入，替换或显式更新，则唯一允许的值为DEFAULT。
视图中的生成列被认为是可更新的，因为可以对其进行分配。但是，如果明确更新了该列，则唯一允许的值为 DEFAULT。

生成列用例

例如：

虚拟生成列可以用作简化和统一查询的方法。可以将复杂条件定义为生成列，并从表上的多个查询中引用该条件，以确保所有条件都使用完全相同的条件。
存储的生成列可用作复杂条件的物化缓存，这些条件需要快速计算。
生成列可以模拟功能索引：使用生成列定义功能表达式并为其编制索引。这对于处理无法直接索引的类型的列（例如，JSON列）很有用。
对于存储的生成列，此方法的缺点是值存储两次。一次作为生成列的值，一次作为索引。
如果为生成列建立索引，则优化器将识别与列定义匹配的查询表达式，并在查询执行期间适当地使用该列中的索引，即使查询未按名称直接引用该列也是如此。

MySQL支持在生成列上建立索引。例如：

CREATE TABLE t1 (f1 INT, gc INT AS (f1 + 1) STORED, INDEX (gc));

alter 生成列

ALTER TABLE允许生成列操作是ADD， MODIFY，和CHANGE。

1.可以添加生成列。

CREATE TABLE t1 (c1 INT);
ALTER TABLE t1 ADD COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) STORED;

2.可以修改生成列的数据类型和表达式。

CREATE TABLE t1 (c1 INT, c2 INT GENERATED ALWAYS AS (c1 + 1) STORED);
ALTER TABLE t1 MODIFY COLUMN c2 TINYINT GENERATED ALWAYS AS (c1 + 5) STORED;

3.如果没有其他列引用生成列，则可以重命名或删除它们。

CREATE TABLE t1 (c1 INT, c2 INT GENERATED ALWAYS AS (c1 + 1) STORED);
ALTER TABLE t1 CHANGE c2 c3 INT GENERATED ALWAYS AS (c1 + 1) STORED;
ALTER TABLE t1 DROP COLUMN c3;

4.虚拟生成列不能更改为存储的生成列，
反之亦然。要解决此问题，请删除该列，然后添加新定义。

CREATE TABLE t1 (c1 INT, c2 INT GENERATED ALWAYS AS (c1 + 1) VIRTUAL);
ALTER TABLE t1 DROP COLUMN c2;
ALTER TABLE t1 ADD COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) STORED;

5.可以将未生成列更改为存储的，但不能更改为虚拟生成列。

CREATE TABLE t1 (c1 INT, c2 INT);
ALTER TABLE t1 MODIFY COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) STORED;

6.可以将已存储但不是虚拟生成列更改为未生成列。存储的生成的值成为非生成列的值。

CREATE TABLE t1 (c1 INT, c2 INT GENERATED ALWAYS AS (c1 + 1) STORED);
ALTER TABLE t1 MODIFY COLUMN c2 INT;

7.ADD COLUMN不是对存储的列的就地操作
（无需使用临时表即可完成），因为表达式必须由服务器评估。对于存储的列，索引更改已就位，而表达式更改未就位。列注释的更改已就位。

8.对于非分区表，ADD COLUMN 和DROP COLUMN是虚拟列的就地操作。
但是，添加或删除虚拟列不能与其他ALTER TABLE操作结合使用。

9.对于分区表，ADD COLUMN和 DROP COLUMN没有对虚拟列就地操作。

10.InnoDB支持虚拟生成列上的二级索引。在虚拟生成列上添加或删除二级索引是就地操作。

11.当将VIRTUAL生成列添加到表中或对其进行修改时，不能确保生成列表达式所计算的数据不会超出该列的范围。这可能导致返回不一致的数据以及意外失败的语句。为了允许控制是否对此类列，ALTER TABLE支持 WITHOUT VALIDATION和WITH VALIDATION子句进行验证：

使用WITHOUT VALIDATION（如果未指定任何子句，则为默认值），将执行就地操作（如果可能），不检查数据完整性，并且语句执行更快。但是，如果值超出范围，以后从表中读取数据可能会报告该列的警告或错误。
使用WITH VALIDATION，ALTER TABLE复制表。如果发生超出范围的错误或任何其他错误，该语句将失败。因为执行了表复制，所以语句花费的时间更长。
WITHOUT VALIDATION并且WITH VALIDATION只有被允许ADD COLUMN，CHANGE COLUMN和 MODIFY COLUMN操作。否则，将 ER_WRONG_USAGE发生错误。

12.从MySQL 5.7.10开始，如果表达式求值导致截断或向函数提供错误的输入，则该 ALTER TABLE语句将以错误终止并拒绝DDL操作。