小巧。快速。可靠。
三选二。
生成列
► 目录

1. 简介

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

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列。只有VIRTUAL列可以使用ALTER TABLE添加。

2.2. 功能

  1. 生成列可以具有数据类型。SQLite尝试使用与普通列相同的 亲和性规则将生成表达式的结果转换为该数据类型。

  2. 生成列可以具有NOT NULL、CHECK和UNIQUE约束以及外键约束，就像普通列一样。

  3. 生成列可以参与索引，就像普通列一样。

  4. 生成列的表达式可以引用表中声明的任何其他列，包括其他生成列，只要该表达式不直接或间接地引用自身即可。

  5. 生成列可以在表定义中的任何位置出现。生成列可以散布在普通列之间。不必将生成列放在表定义中列列表的末尾，如上面的示例所示。

2.3. 限制

  1. 生成列不能具有 默认值（它们不能使用“DEFAULT”子句）。生成列的值始终是“AS”关键字后面的表达式指定的值。

  2. 生成列不能用作 主键 的一部分。（SQLite的未来版本可能会放宽对STORED列的此约束。）

  3. 生成列的表达式只能引用常量文字和同一行内的列，并且只能使用标量 确定性函数。表达式不能使用子查询、聚合函数、窗口函数或表值函数。

  4. 生成列的表达式可以引用同一行中的其他生成列，但任何生成列都不能直接或间接地依赖于自身。

  5. 生成列的表达式不能直接引用 ROWID，但可以引用 INTEGER PRIMARY KEY 列，这通常是同一件事。

  6. 每个表必须至少包含一个非生成列。

  7. 无法 ALTER TABLE ADD COLUMN STORED列。但是，可以添加VIRTUAL列。

  8. 生成列的数据类型和 排序规则仅由列定义上的数据类型和 COLLATE子句确定。GENERATED ALWAYS AS表达式的的数据类型和排序规则对列本身的数据类型和排序规则没有影响。

  9. 生成列不包含在 PRAGMA table_info 语句提供的列列表中。但它们包含在更新的 PRAGMA table_xinfo 语句的输出中。

3. 兼容性

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

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