SQL 中的生成列/计算列以及主流数据库实现

文章目录

        什么是生成列？
        Oracle 中的虚拟列
        MySQL 中的生成列
        SQL Server 中的计算列
        PostgreSQL 中的存储生成列
        SQLite 中的生成列

生成列
什么是生成列？

在 SQL 数据库中，生成列（Generated Column）是指由表中其他字段计算得到的列，因此也称为计算列（Computed Column）。

生成列存在两种类型：存储（stored）生成列和虚拟（virtual）生成列。存储生成列和普通列类似，在插入或者更新数据时自动计算并且存储该列的值，需要占用存储空间；虚拟生成列不需要占用存储空间，只在读取时进行计算。因此，虚拟生成列就像是一个视图（字段的视图），而存储生成列就像是一个物化视图（实时更新）。

⚠️我们无法直接插入或者更新生成列的值，它的值由数据库自动生成和更新。

生成列的常见用途包括：

虚拟生成列可以用来简化和统一查询。我们可以将复杂的查询条件定义成一个生成列，然后在查询该表时使用，从而确保所有的查询都使用相同的判断条件。
存储生成列可以作为查询条件的物化缓存（materialized cache），减少查询时的计算成本。
生成列可以模拟函数索引：定义一个基于函数表达式的生成列并且创建索引。对于存储型的生成列，这种方式需要占用更多的存储。

各种主流 SQL 数据库对于生成列/计算列的支持如下：

• 参考下文中的具体讨论。
  Oracle 中的虚拟列

Oracle 11g 开始支持虚拟的生成列，简称虚拟列。定义虚拟列的语法如下：

column [ datatype [ COLLATE column_collation_name ] ]
[ GENERATED ALWAYS ] AS (column_expression) [ VIRTUAL ]

如果省略了 datatype，虚拟列的类型由表达式 column_expression 决定；GENERATED ALWAYS AS表示定义生成列；表达式只能包含当前表中的字段、常量以及确定性的函数；VIRTUAL表示虚拟列，可以省略。以下是一个创建虚拟列的示例：

CREATE TABLE t_circle(
id INTEGER PRIMARY KEY,
x NUMERIC(20,10) NOT NULL,
y NUMERIC(20,10) NOT NULL,
radius NUMERIC(20,10) NOT NULL,
perimeter NUMERIC(20,10) GENERATED ALWAYS AS (2 * 3.14159265 * radius) VIRTUAL
);

ALTER TABLE t_circle ADD (area AS (3.14159265 * radius * radius));

首先，使用CREATE TABLE语句为表 t_circle 创建了一个虚拟列 perimeter；然后使用ALTER TABLE语句为其增加了一个虚拟列 area。

接下来我们插入一些数据：

INSERT INTO t_circle(id, x, y, radius) VALUES (1, 2, 2, 5);
SELECT * FROM t_circle;

ID	X	Y	RADIUS	PERIMETER	AREA
1	2	2	5	31.4159265	78.53981625

INSERT INTO t_circle(id, x, y, radius ,perimeter) VALUES (2, 0, 0, 1, 6.28318530);
SQL Error [54013] [99999]: ORA-54013: INSERT operation disallowed on virtual columns

第一个插入语句没有指定虚拟列的值，在查询时由数据库自动计算；第二个插入语句指定了虚拟列的值，执行失败。

Oracle 中的虚拟列支持索引，我们为 t_circle 中的虚拟列创建两个索引：

CREATE UNIQUE INDEX idx11 ON t_circle(perimeter);
CREATE INDEX idx12 ON t_circle(area);

除了支持索引之外，虚拟列还支持NOT NULL、UNIQUE、主键、CHECK以及外键约束，但是不支持DEFAULT默认值。

使用 Oracle 虚拟列需要注意以下事项：

如果表达式 column_expression 引用了具有列级安全的字段，虚拟列不会继承基础列上的安全规则。此时，用户需要自己确保虚拟列数据的安全，可以为虚拟列再设置一个列级安全策略或者使用函数对数据进行模糊处理。例如，信用卡号通常会使用一个列级安全策略进行包含，允许客服中心的员工查看最后四位数字以便验证信息。此时，可以定义一个虚拟列保存信用卡号的后四位子串。
基于虚拟列创建的索引等价于函数索引。
不能直接更新虚拟列。因此，不能在UPDATE语句的SET子句中设置虚拟列。不过，可以在UPDATE语句的WHERE子句中使用虚拟列。同理，可以在DELETE语句的WHERE子句中使用虚拟列。
在FROM子句中使用包含虚拟列的表的查询语句可以缓存结果，具体参考Oracle 官方文档。
表达式 column_expression 可以引用明确指定了 DETERMINISTIC 属性的 PL/SQL 函数。但是，如果随后替换了该函数的定义，基于虚拟列的对象不会失效。此时如果表中包含数据，而且虚拟列用于了约束、索引、物化视图或者查询结果缓存，访问虚拟列的查询可能返回不正确的结果。因此，为了替换虚拟列中的确定性函数：
    禁用然后再启用虚拟列上的约束。
    重建虚拟列上的索引。
    完全刷新基于虚拟列的物化视图。
    刷新访问了该虚拟列的查询结果缓存。
    重新收集该表的统计信息。
虚拟列可以是 INVISIBLE 列，虚拟列的表达式中可以包含 INVISIBLE 列。

Oracle 中的虚拟列存在以下限制：

只能为关系型的堆表创建虚拟列，索引组织表、外部表、对象表、聚簇表以及临时表不支持虚拟列。
虚拟列表达式 column_expression 存在以下限制：
    不能引用其他虚拟列。
    只能引用当前表中的列。
    可以引用确定性的自定义函数，但此时该虚拟列不能作为分区字段。
    表达式的结果必须是一个标量值。
虚拟列不支持 Oracle 提供的数据类型、用户定义类型以及 LOB 和 LONG RAW 类型。
虚拟列可以作为分区字段，但是作为分区字段的虚拟列中不能包含 PL/SQL 函数。

参考文档：Oracle 官方文档。
MySQL 中的生成列

MySQL 5.7 引入了生成列，支持虚拟和存储两种类型的生成列。定义生成列的语法如下：

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

其中，GENERATED ALWAYS可以省略，AS定义了生成列的表达式；VIRTUAL表示创建虚拟生成列，虚拟列的值不会存储，而是在读取时BEFORE触发器之后立即计算；STORED表示存储生成列；默认创建的是VIRTUAL生成列。

我们创建一个表 t_circle：

CREATE TABLE t_circle(
id INTEGER PRIMARY KEY,
x NUMERIC(20,10) NOT NULL,
y NUMERIC(20,10) NOT NULL,
radius NUMERIC(20,10) NOT NULL,
perimeter NUMERIC(20,10) AS (2 * 3.14159265 * radius)
);

ALTER TABLE t_circle ADD area NUMERIC(20,10) AS (3.14159265 * radius * radius) STORED;

其中，perimeter 是一个虚拟的生成列；area 是一个存储的生成列。MySQL 生成列还支持NOT NULL、UNIQUE、主键、CHECK以及外键约束，但是不支持DEFAULT默认值。

MySQL 生成列的表达式必须遵循以下规则：

允许使用常量、确定性的内置函数以及运算符。确定性函数意味着对于表中的相同数据，多次调用返回相同的结果，与当前用户无关。非确定性的函数包括 CONNECTION_ID()、CURRENT_USER()、NOW() 等。
不允许使用存储函数和自定义函数。
不允许使用存储过程和函数的参数。
不允许使用变量（系统变量、自定义变量或者存储程序中的本地变量）。
不允许子查询。
允许引用表中已经定义的其他生成列；允许引用任何其他非生成列，无论这些列出现的位置在前面还是后面。
不允许使用 AUTO_INCREMENT 属性。
不允许使用 AUTO_INCREMENT 字段作为生成列的基础列。
如果表达式的运算导致截断或者为函数提供了不正确的输入，CREATE TABLE 将会失败。

另外，如果表达式的结果类型与字段定义中的数据类型不同，将会执行隐式的类型转换。

接下来我们运行一些数据测试：

INSERT INTO t_circle(id, x, y, radius) VALUES (1, 2, 2, 5);
SELECT * FROM t_circle;

id	x	y	radius	perimeter	area
1	2.0000000000	2.0000000000	5.0000000000	31.4159265000	78.5398162500

INSERT INTO t_circle(id, x, y, radius ,perimeter) VALUES (2, 0, 0, 1, 6.28318530);
SQL Error [3105] [HY000]: The value specified for generated column ‘perimeter’ in table ‘t_circle’ is not allowed.

第一个插入语句没有指定生成列的值，由数据库自动计算；第二个插入语句为 perimeter 提供了数据，执行失败；不过可以使用DEFAULT关键字。

MySQL 支持存储生成列的索引，InnoDB 还支持虚拟生成列的二级索引，具体参考 MySQL 官方文档 。我们使用以下语句为 t_circle 表的两个生成列分别创建两个索引：

CREATE UNIQUE INDEX idx1 ON t_circle(perimeter);
CREATE INDEX idx2 ON t_circle(area);

另外，使用 MySQL 生成列还需要注意以下事项：

对于CREATE TABLE ... LIKE语句，创建的新表将会保留原表中的生成列信息。
对于CREATE TABLE ... SELECT语句，创建的新表不会保留查询语句的原表中的生成列信息；并且SELECT语句不能为目标表中的生成列赋值。
允许基于生成列的分区，具体参考 MySQL 官方文档。
存储生成列上的外键约束不能为ON UPDATE操作指定CASCADE、SET NULL或者SET DEFAULT选项，也不能为ON DELETE操作指定SET NULL 或者SET DEFAULT选项。
存储生成列的基础列上的外键约束也不能为ON UPDATE或者ON DELETE操作指定CASCADE、SET NULL或者SET DEFAULT选项。
外键约束中的被引用字段不能是虚拟的生成列。
触发器不能通过 NEW.col_name 或者 OLD.col_name 引用生成列。
对于INSERT、REPLACE以及UPDATE，如果要明确指定生成列的值，只能使用DEFAULT。视图中的生成列属于可更新列，但是也只能使用DEFAULT显式更新。

参考文档：MySQL 官方文档。
SQL Server 中的计算列

更多请见：http://www.mark-to-win.com/tutorial/51672.html