生成的列

一、简介

生成列（有时也称为“计算列”）是表中的列，其值是同一行中其他列的函数。可以读取生成的列，但不能直接写入它们的值。更改生成列的值的唯一方法是修改用于计算生成列的其他列的值。

2.语法

从句法上讲，生成的列是使用“GENERATED ALWAYS”列约束指定的。例如：

CREATE TABLE t1(
   a INTEGER PRIMARY KEY,
   b INT,
   c TEXT,
   d INT GENERATED ALWAYS AS (a*abs(b)) VIRTUAL,
   e TEXT GENERATED ALWAYS AS (substr(c,b,b+1)) STORED
);

上面的语句有三个普通列，“a”（主键）、“b”和“c”，以及两个生成的列“d”和“e”。

约束开头的“GENERATED ALWAYS”关键字和结尾的“VIRTUAL”或“STORED”关键字都是可选的。只有“AS”关键字和带括号的表达式是必需的。如果省略尾随的“VIRTUAL”或“STORED”关键字，则默认为 VIRTUAL。因此，上面的示例语句可以简化为：

CREATE TABLE t1(
   a INTEGER PRIMARY KEY,
   b INT,
   c TEXT,
   d INT AS (a*abs(b)),
   e TEXT AS (substr(c,b,b+1)) STORED
);

2.1. VIRTUAL 与 STORED 列

生成的列可以是 VIRTUAL 或 STORED。VIRTUAL 列的值是在读取时计算的，而 STORED 列的值是在写入行时计算的。STORED 列占用数据库文件中的空间，而 VIRTUAL 列在读取时使用更多的 CPU 周期。

从 SQL 的角度来看，STORED 和 VIRTUAL 列几乎完全相同。针对任一类生成列的查询产生相同的结果。唯一的功能区别是不能使用 ALTER TABLE ADD COLUMN命令添加新的 STORED 列。使用 ALTER TABLE 只能添加 VIRTUAL 列。

2.2. 能力

生成的列可以有一个数据类型。SQLite 尝试使用与普通列相同的关联规则将生成表达式的结果转换为该数据类型。
生成的列可能有 NOT NULL、CHECK 和 UNIQUE 约束，以及外键约束，就像普通列一样。
生成的列可以像普通列一样参与索引。
生成列的表达式可以引用表中任何其他已声明的列，包括其他生成的列，只要该表达式不直接或间接引用回自身即可。
生成的列可以出现在表定义中的任何位置。生成的列可以穿插在普通列之间。不必将生成的列放在表定义中列列表的末尾，如上面的示例所示。

2.3. 限制

生成的列可能没有默认值（它们可能不使用“DEFAULT”子句）。生成列的值始终是“AS”关键字后的表达式指定的值。
生成的列不能用作PRIMARY KEY的一部分。（SQLite 的未来版本可能会放宽对 STORED 列的限制。）
生成列的表达式只能引用常量文字和同一行中的列，并且只能使用标量确定性函数。表达式不能使用子查询、聚合函数、窗口函数或表值函数。
生成列的表达式可以引用同一行中的其他生成列，但生成列不能直接或间接依赖于自身。
生成列的表达式可能不会直接引用ROWID，但它可以引用INTEGER PRIMARY KEY列，这通常是同一件事。
每个表必须至少有一个非生成列。
不可能通过ALTER TABLE ADD COLUMN存储列。但是，可以添加一个 VIRTUAL 列。
生成列的数据类型和整理顺序仅由列定义中的数据类型和COLLATE 子句确定。GENERATED ALWAYS AS 表达式的数据类型和整理顺序对列本身的数据类型和整理顺序没有影响。

3.兼容性

SQLite 版本 3.31.0 (2020-01-22) 添加了对生成列的支持。如果 SQLite 的早期版本试图读取其模式中包含生成列的数据库文件，则该早期版本会将生成列语法视为错误，并报告数据库模式已损坏。

澄清一下：SQLite 版本 3.31.0 可以读取和写入任何由 SQLite 3.0.0 (2004-06-18) 之前版本创建的数据库。并且，SQLite 3.31.0 之前的早期版本可以读取和写入由 SQLite 3.31.0 及更高版本创建的数据库，只要数据库模式不包含早期版本无法理解的功能，例如生成的列版本。仅当您使用 SQLite 3.31.0 或更高版本创建包含生成列的新数据库，然后尝试使用不理解生成列的早期版本的 SQLite 读取或写入该数据库文件时，才会出现问题。