这是我参与8月更文挑战的第14天，活动详情查看：8月更文挑战

关于生成列、衍生列或计算列

由上一篇中通用处理中的冗余列，可以引出生成列、衍生列或计算列，因为生成列正好可以满足在原始列的基础上，创建新的冗余列的要求。

当然生成列的作用不仅于此，而是有着更广泛的应用。

文章对生成列/计算列的介绍相当完整完备，除了SQL Server部分的示例不正确以外，其他部分都很详细，很好的一篇文章，因此参考原文并进行了小修改修正，同时去除了生成列注意事项或限制的介绍，一是可以直接参考原文；二是很多这部分中介绍的概念或术语需要对数据库有深入的了解。

什么是生成列？

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

生成列存在两种类型：存储（stored）生成列和虚拟（virtual）生成列。

存储生成列和普通列类似，在插入或者更新数据时自动计算并且存储该列的值，需要占用存储空间；虚拟生成列不需要占用存储空间，只在读取时进行计算。

虚拟生成列就像是一个视图（字段的视图），而存储生成列就像是一个物化视图（实时更新）。

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

生成列的常见用途

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

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

生成列	Oracle	MySQL	SQL Server	PostgreSQL	SQLite
存储生成列	❌	✔️	✔️	✔️	✔️
虚拟生成列	✔️	✔️	✔️	❌	✔️
约束支持	主键约束 NOT NULL UNIQUE CHECK 外键约束	主键约束 NOT NULL UNIQUE CHECK 外键约束	主键约束 NOT NULL UNIQUE CHECK 外键约束	主键约束 NOT NULL UNIQUE CHECK 外键约束	NOT NULL UNIQUE CHECK 外键约束
索引支持	✔️	✔️	✔️	✔️	✔️

所有的生成列都不支持默认值约束。

Oracle 中的虚拟列

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

column [ datatype [ COLLATE column_collation_name ] ]
  [ GENERATED ALWAYS ] AS (column_expression) [ VIRTUAL ]
复制代码

虚拟列的类型由表达式 column_expression 决定，要与datatype对应；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);
复制代码

详细参考：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 是一个存储的生成列。

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

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

MariaDB [test]> INSERT INTO t_circle(id, x, y, radius) VALUES (1, 2, 2, 5);
Query OK, 1 row affected (0.007 sec)

MariaDB [test]> SELECT * FROM t_circle;
+----+--------------+--------------+--------------+---------------+---------------+
| id | x            | y            | radius       | perimeter     | area          |
+----+--------------+--------------+--------------+---------------+---------------+
|  1 | 2.0000000000 | 2.0000000000 | 5.0000000000 | 31.4159265000 | 78.5398162500 |
+----+--------------+--------------+--------------+---------------+---------------+
1 row in set (0.000 sec)

MariaDB [test]> INSERT INTO t_circle(id, x, y, radius ,perimeter) VALUES (2, 0, 0, 1, 6.28318530);
ERROR 1906 (HY000): The value specified for generated column 'perimeter' in table 't_circle' ignored
复制代码

MySQL 支持存储生成列的索引，InnoDB 还支持虚拟生成列的二级索引，具体参考 MySQL 官方文档。

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

CREATE UNIQUE INDEX idx1 ON t_circle(perimeter);
CREATE INDEX idx2 ON t_circle(area);
复制代码

参考文档：MySQL 官方文档。

SQL Server 中的计算列

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

SQL Server中的计算列不用指定数据类型。

<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约束。

创建一个表 t_circle：

CREATE TABLE t_circle(
   id INTEGER PRIMARY KEY,
   x NUMERIC NOT NULL,
   y NUMERIC NOT NULL,
   radius NUMERIC NOT NULL,
   perimeter AS (2 * 3.14159265 * radius),
   area 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;
----
id	x	y	radius	perimeter	area	dt
1	2	2	5	31.41592650	78.539816	2021-08-13 11:42:49.733

INSERT INTO t_circle(id, x, y, radius ,perimeter) VALUES (2, 0, 0, 1, 6.28318530);
 Msg 271, Level 16, State 1, Line 1
 The column "perimeter" cannot be modified because it is either a computed column or is the result of a UNION operator.
复制代码

多次运行的话 dt 字段将会返回不同的日期。

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.
复制代码

dt 列不支持索引，因为它包含了不确定性的函数，每次调用时它的值可能发生变化。

参考文档：SQL Server 官方文档。

PostgreSQL 中的生成列

PostgreSQL 12 开始支持生成列，支持虚拟的和存储的两种类型的生成列。

PostgreSQL currently implements only stored generated columns.

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;
ALTER TABLE t_circle ADD area2 NUMERIC CONSTRAINT generated_col GENERATED ALWAYS AS (3.14159265 * radius * radius) STORED;
复制代码

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

如下，向表中插入数据测试：

shop=# INSERT INTO t_circle VALUES (1, 2, 2, 5);
INSERT 0 1
shop=# SELECT * FROM t_circle;
 id | x | y | radius |  perimeter  |    area     |    area2
----+---+---+--------+-------------+-------------+-------------
  1 | 2 | 2 |      5 | 31.41592650 | 78.53981625 | 78.53981625
(1 行记录)


shop=# INSERT INTO t_circle(id, x, y, radius ,perimeter) VALUES (2, 0, 0, 1, 6.28318530);
错误:  无法插入到列"perimeter"   SQL Error [42601]: ERROR: cannot insert into column "perimeter"
描述:  列"perimeter"是生成的列.     Detail: Column "perimeter" is a generated column.
复制代码

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

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

shop=# create unique index idx1 on t_circle(perimeter);
CREATE INDEX
shop=# create index idx2 on t_circle(area);
CREATE INDEX
复制代码

PostgreSQL 中外部表可以支持生成列，参考 CREATE FOREIGN TABLE。

生成列的访问权限控制与其表达式中引用的基础列无关。因此，一个用户可能无法读取基础列中的数据，但是可以读取生成列的数据，实现特定的数据安全访问。

参考文档：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
);
复制代码

⚠️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"
复制代码

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

为 t_circle 表的两个生成列分别创建两个索引：

sqlite> create unique index idx1 on t_circle(perimeter);
sqlite> create index idx2 on t_circle(area);
复制代码

参考文档：SQLite 官方文档。

文章版权归作者所有，未经允许请勿转载。

THE END

后端

jvm详解（二）—— 类加载子系统

并发编程概览

高级UI之Canvas深度分析—变换技巧，状态保存

百度移动搜索优化：移动站点的收录、排名等说明

Vortex Portal远程PHP文件包含漏洞

Spring Boot注解全攻略(九)：@Profile

什么是生成列？| 数据库教程12：关于生成列、衍生列或计算列的探讨，及每种数据库中的实现