SQLite 生成列generated always

生成列，也叫计算列，其是依其它列为变量，使用某些函数方式，自动生成的新的列。生成的列可以读取，但不能直接写入它们的值。更改生成列的值的唯一方法是修改用于计算生成列的其他列的值。

注意：

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

语法

生成列使用关键字 GENERATED ALWAYS AS来实现。

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
);

生成列示例

如我们创建一个表t_add,含有a,b列，c列是a,b列之和。

sqlite> create table t_add(a,
   ...> b,
   ...> c generated alaways as(a+b)
   ...> );
);

然后我们添加几组数据.

sqlite> insert into t_add(a,b) values(1,2),(3,4);

再查看数据:

sqlite> select * from t_add;
+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 2 | 3 |
| 3 | 4 | 7 |
+---+---+---+

可以看到，列c为a列和b列计算的合。

生成列分类

生成列分为两种:

• virtual:默认方式，虚拟列的值在读取时计算，故虚拟列在读取时占用更多的CPU周期。
• STORED：存储列的值在写入行时计算，故存储列占用数据库文件中的空间。

  在创建生成列时，如未指定生成列方式的关键字，默认为virtual方式。

sqlite> create table t_add2(a,
   ...> b,
   ...> c generate as (a+b) virtual,
   ...> d generate as (a+b) stored
   ...> );

存储列相关的值在被修改时，其相关的生成列的值会自动被重新计算存储。

sqlite> insert into t_add2(a,b) values(1,2),(3,4);
sqlite> select * from t_add2;
+---+---+---+---+
| a | b | c | d |
+---+---+---+---+
| 1 | 2 | 3 | 3 |
| 3 | 4 | 7 | 7 |
+---+---+---+---+
sqlite> update t_add2 set a=2 where b=4;
sqlite> select * from t_add2;
+---+---+---+---+
| a | b | c | d |
+---+---+---+---+
| 1 | 2 | 3 | 3 |
| 2 | 4 | 6 | 6 |
+---+---+---+---+
sqlite>

可以看到，当第二行a的值变为2时，其存储列的值d也会因a的值变化而重新计算。

生成列的特点：

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

生成列的不足：

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