前言
-
索引（index） 在SQL中可以大大提高 查询效率。
-
SQL索引列中，如果使用了运算和函数，索引将无法生效。
-
如果索引列的数据量大且稳定，可以使用 生成列 来固定对索引列的计算和函数运算，并对该生成列添加索引提高查询效率。
如何创建生成列
官网原文：https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html
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 生成列
官网原文：
https://dev.mysql.com/doc/refman/8.0/en/alter-table-generated-columns.html
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操作。
- ALTER TABLE更改列的默认值 的语句也 col_name可能会更改使用引用该列的生成列表达式的值 。
- 因此，从MySQL 5.7.13开始， 如果任何生成列表达式使用，则更改列定义的操作将导致表重建 。
4934
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
