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

代码迷途
2,057 阅读9分钟

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

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

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

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

下面部分主要参考自SQL 中的生成列/计算列以及主流数据库实现。

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

什么是生成列？

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

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

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

虚拟生成列就像是一个视图（字段的视图），而存储生成列就像是一个物化视图（除了它的更新是自动的）。

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

生成列的常见用途

  • 虚拟生成列可以用来简化和统一查询。我们可以将复杂的查询条件定义成一个生成列，然后在查询该表时使用，从而确保所有的查询都使用相同的判断条件。

  • 存储生成列可以作为查询条件的物化缓存（materialized cache），减少查询时的计算成本。

  • 生成列可以模拟函数索引：定义一个基于函数表达式的生成列并且创建索引。对于存储型的生成列，这种方式需要占用更多的存储。

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

生成列OracleMySQLSQL ServerPostgreSQLSQLite
存储生成列❌✔️✔️✔️✔️
虚拟生成列✔️✔️✔️❌✔️
约束支持主键约束
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 官方文档。

🎴 在MariaDB中，还支持使用 PERSISTENT 关键字作为 STORED 的别名，即PERSISTENT也表示存储生成列。

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 官方文档。

avatar
文章
阅读
粉丝