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

作者: 不剪发的Tony老师
毕业于北京航空航天大学，十多年数据库管理与开发经验，目前在一家全球性的金融公司从事数据库架构设计。CSDN学院签约讲师以及GitChat专栏作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net


文章目录

        什么是生成列？
        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;

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;

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 中的计算列

SQL Server 2005 增加了生成列的支持，称为计算列。计算列的完整定义如下：

<computed_column_definition> ::=
column_name AS computed_column_expression
[ PERSISTED [ NOT NULL ] ]
[
[ CONSTRAINT constraint_name ]
{ PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[
WITH FILLFACTOR = fillfactor
| WITH ( <index_option> [ , …n ] )
]
[ ON { partition_scheme_name ( partition_column_name )
| filegroup | “default” } ]

| [ FOREIGN KEY ]
    REFERENCES referenced_table_name [ ( ref_column ) ]
    [ ON DELETE { NO ACTION | CASCADE } ]
    [ ON UPDATE { NO ACTION } ]
    [ NOT FOR REPLICATION ]

| CHECK [ NOT FOR REPLICATION ] ( logical_expression )

]

其中，AS表示定义一个计算列；PERSISTED表示需要存储该列的值，即存储型的计算列； 计算列的表达式可以使用其他非计算列、常量、函数、变量，但是不能使用子查询或别名数据类型。SQL Server 中的计算列支持主键、UNIQUE约束，存储计算列还支持NOT NULL、外键以及CHECK约束；但是计算列不支持DEFAULT默认值，也不能作为外键中的被引用字段。

我们创建一个表 t_circle：

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

ALTER TABLE t_circle ADD dt AS GETDATE();

其中，perimeter 是一个虚拟的计算列；area 是一个存储的计算列；ALTER TABLE语句增加了第三个计算列，使用了一个不确定性函数 GETDATE()。

我们测试一下数据插入和查询：

INSERT INTO t_circle VALUES (1, 2, 2, 5);
SELECT * FROM t_circle;

INSERT INTO t_circle(id, x, y, radius ,perimeter) VALUES (2, 0, 0, 1, 6.28318530);
SQL Error [271] [S0001]: The column “perimeter” cannot be modified because it is either a computed column or is the result of a UNION operator.

第一个插入语句没有指定生成列的值，由数据库自动计算；查询返回了所有的字段，多次运行的话 dt 字段将会返回不同的日期；第二个插入语句为 perimeter 提供了数据，执行失败；INSERT和UPDATE语句不能为生成列指定值。

SQL Server 支持基于计算列的索引，但是需要满足一定的条件：

create unique index idx1 on t_circle(perimeter);
create index idx2 on t_circle(area);

create index idx3 on t_circle(dt);
SQL Error [2729] [S0001]: Column ‘dt’ in table ‘t_circle’ cannot be used in an index or statistics or as a partition key because it is non-deterministic.

前两个计算列不包含不确定性的函数，可以创建索引，或者 PRIMARY KEY 和 UNIQUE 约束；但是 dt 列不支持索引，因为它包含了不确定性的函数，每次调用时它的值可能发生变化。

参考文档：SQL Server 官方文档。
PostgreSQL 中的存储生成列

PostgreSQL 12 提供了生成列，目前只支持存储型的生成列。通过在CREATE TABLE或者ALTER TABLE语句中指定字段的GENERATED ALWAYS AS约束来创建一个生成列：

column_name data_type [ COLLATE collation ]
[ CONSTRAINT constraint_name ]
GENERATED ALWAYS AS ( generation_expr ) STORED

其中，GENERATED ALWAYS AS表示创建生成列；generation_expr 指定了生成列的表达式；STORED意味着需要存储该列的值。例如以下语句：

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

ALTER TABLE t_circle ADD area NUMERIC GENERATED ALWAYS AS (3.14159265 * radius * radius) STORED;

首先，CREATE TABLE语句为表 t_circle 定义了一个生成列 perimeter，表示圆的周长。然后，使用ALTER TABLE语句增加一个生成列 area ，表示圆的面积。

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

INSERT INTO t_circle VALUES (1, 2, 2, 5);
SELECT * FROM t_circle;

INSERT INTO t_circle(id, x, y, radius ,perimeter) VALUES (2, 0, 0, 1, 6.28318530);
SQL Error [42601]: ERROR: cannot insert into column “perimeter”
Detail: Column “perimeter” is a generated column.

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

PostgreSQL 中的生成列支持索引，我们使用以下语句为 t_circle 表的两个生成列分别创建两个索引：

create unique index idx1 on t_circle(perimeter);
create index idx2 on t_circle(area);

第一个索引 idx1 是唯一索引，第二个索引 idx2 是普通索引。

除了支持索引之外，PostgreSQL 生成列还支持NOT NULL、UNIQUE、主键、CHECK以及外键约束，但是不支持DEFAULT默认值。另外，在 PostgreSQL 中使用生成列存在一些限制：

生成列的表达式只能使用不可变（IMMUTABLE）函数，不能使用子查询或者引用非当前数据行的任何其他数据。
生成列的表达式不能引用其他的生成列。
生成列的表达式不能引用除了 tableoid 之外的其他系统字段。
生成列不能指定默认值或者标识列。
生成列不能作为分区键的一部分。
外部表可以支持生成列，参考 CREATE FOREIGN TABLE。

使用生成列时还需要注意以下事项：

生成列的访问权限控制与其表达式中引用的基础列无关。因此，一个用户可能无法读取基础列中的数据，但是可以读取生成列的数据，实现特定的数据安全访问。
从逻辑上讲，生成列的值在 BEFORE 触发器之后进行更新。在 BEFORE 触发器中对基础列的修改会同步到生成列中；但是反过来，在 BEFORE 触发器中不能访问生成列的值。

参考文档：PostgreSQL 生成列。
SQLite 中的生成列

SQLite 3.31.0 开始支持生成列，语法上通过“GENERATED ALWAYS”字段约束实现：

其中的GENERATED ALWAYS可以省略；STORED表示存储型的生成列，VIRTUAL表示虚拟型的生成列，省略的话默认为后者。例如以下示例表：

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

其中 radius 表示圆的半径；perimeter 是一个虚拟生成列，表示圆的周长；area 是一个存储生成列，表示圆的面积。

⚠️SQLite 中的ALTER TABLE ADD COLUMN命令只能增加VIRTUAL生成列，不支持STORED生成列。

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

sqlite> INSERT INTO t_circle VALUES (1, 2, 2, 5);
sqlite> SELECT * FROM t_circle;
1|2|2|5|31.4159265|78.53981625

sqlite> INSERT INTO t_circle(id, x, y, radius ,perimeter) VALUES (2, 0, 0, 1, 6.28318530);
Error: cannot INSERT into generated column “perimeter”

第一个插入语句执行成功，查询返回了两个生成列的值；第二个插入语句尝试指定生成列的值，返回了错误。

SQLite 中的生成列支持以下功能：

生成列可以指定数据类型。SQLite 使用与普通字段相同的类型亲和性将表达式的结构转换为该类型。
生成列可以像普通字段一样指定 NOT NULL、CHECK 以及 UNIQUE 约束和外键约束。
生成列可以像普通字段一样支持索引。
生成列的表达式可以引用表中的任何列，包括其他的生成列，只要该表达式不会直接或者间接引用自身。
生成列可以出现在表定义中的任何位置。生成列可以出现在普通列的中间，不一定需要位于字段列表的最后。

我们可以使用以下语句为 t_circle 表的两个生成列分别创建两个索引：

sqlite> create unique index idx1 on t_circle(perimeter);
sqlite> create index idx2 on t_circle(area);

另一方面，SQLite 中的生成列目前还存在一些限制：

生成列不能指定默认值（DEFAULT）。生成列的值总是由 AS 关键字后的表达式决定。
生成列不能作为 PRIMARY KEY 的一部分。将来的 SQLite 可能会支持基于 STORED 生成列的主键。
生成列的表达式只能引用常量字面值和其他字段，或者确定性的标量函数。表达式中不能使用子查询、聚合函数、窗口函数或者表函数。
生成列的表达式可以引用其他的生成列，但是不能引用直接或者间接依赖于它自身的其他生成列。
生成列的表达式不能直接引用 ROWID，但是可以引用 INTEGER PRIMARY KEY 列，虽然两者效果相同。
每个表至少需要包含一个非生成的普通列。
ALTER TABLE ADD COLUMN 语句不支持 STORED 生成列，但是可以添加 VIRTUAL 生成列。
生成列的数据类型和字符排序顺序由字段定义中的数据类型和 COLLATE 子句决定，与 GENERATED
ALWAYS AS 表达式的数据类型和字符排序顺序无关。