生成的列始终是由某行中的其他列计算得出的列。这些列可简化查询、查询时评估表达式的费用,并可编织索引或作为外键。在本文中,我们将介绍如何在数据库中管理此列类型。
向新表添加生成的列
在以下 CREATE TABLE
代码段中,我们将创建一个表来存储用户的相关信息。我们有 FirstName
和 LastName
的列,并为 FullName
(它由 FirstName
与 LastName
串联而成)定义了一个生成的列。英文括号中的 SQL 称为 generation expression。
您可以将生成的列标记为 STORED
,以节省查询时对表达式求值的费用。因此,只有在插入新行或为现有行更新 FirstName
或 LastName
时,才会计算 FullName
的值。计算值与表中的其他列一起存储。
GoogleSQL
CREATE TABLE Users (
Id STRING(20) NOT NULL,
FirstName STRING(50),
LastName STRING(50),
Age INT64 NOT NULL,
FullName STRING(100) AS (FirstName || ' ' || LastName) STORED
) PRIMARY KEY (Id);
PostgreSQL
CREATE TABLE users (
id VARCHAR(20) NOT NULL,
firstname VARCHAR(50),
lastname VARCHAR(50),
age BIGINT NOT NULL,
fullname VARCHAR(100) GENERATED ALWAYS AS (firstname || ' ' || lastname) STORED,
PRIMARY KEY(id)
);
您可以通过在 DDL 中省略 STORED
属性来创建非存储的生成列。此类生成的列会在查询时求值,可以简化查询。在 PostgreSQL 中,您可以使用 VIRTUAL
属性创建非存储的生成列。
GoogleSQL
FullName STRING(MAX) AS (CONCAT(FirstName, " ", LastName))
PostgreSQL
fullname text GENERATED ALWAYS AS (firstname || ' ' || lastname) VIRTUAL
expression
可以是可分配给列数据类型的任何有效 SQL 表达式,但存在以下限制。表达式只能引用同一表中的列。
表达式不能包含子查询。
包含非确定性函数(例如
PENDING_COMMIT_TIMESTAMP()
、CURRENT_DATE()
和CURRENT_TIMESTAMP()
)的表达式无法转换为STORED
生成的列或编入索引的生成的列。您无法修改
STORED
或编入索引的生成列的表达式。
表达式后的
STORED
属性会将表达式结果与表的其他列一起存储。对任何被引用的列的后续更新都将导致 Spanner 重新求值和存储表达式。非
STORED
的生成列无法标记为NOT NULL
。不允许直接写入生成的列。
生成的列或生成的列引用的任何列都不允许使用列选项
allow_commit_timestamp
。对于编入索引的
STORED
或生成的列,您无法更改该列或该生成的列引用的任何列的数据类型。您无法删除由生成的列引用的列。
您可以将生成的列用作主键,但需遵循以下额外限制:
生成的主键不能引用其他生成的列。
生成的主键最多只能引用一个非键列。
生成的主键不能依赖于包含
DEFAULT
子句的非键列。
使用生成的键列时,请遵循以下规则:
- 读取 API:您必须完全指定键列,包括生成的键列。
- 更新 API:对于
INSERT
、INSERT_OR_UPDATE
和REPLACE
,Spanner 不允许您指定生成的主键列。对于UPDATE
,您可以选择指定生成的主键列。对于DELETE
,您需要完全指定键列,包括生成的键。 - DML:您无法在
INSERT
或UPDATE
语句中显式写入生成的键。 - 查询:通常,我们建议您在查询中将生成的键列用作过滤条件。(可选)如果生成的主键列的表达式仅使用一个列作为引用,则查询可以对引用的列应用等式 (
=
) 或IN
条件。如需了解详情和示例,请参阅创建从值列派生的唯一键。
生成的列可以像任何其他列一样进行查询,如以下示例所示。
GoogleSQL
SELECT Id, FullName
FROM Users;
PostgreSQL
SELECT id, fullname
FROM users;
使用 Fullname
的查询等同于使用生成的表达式的查询。因此,生成的列可以简化查询。
GoogleSQL
SELECT Id, ARRAY_TO_STRING([FirstName, LastName], " ") as FullName
FROM Users;
PostgreSQL
SELECT id, firstname || ' ' || lastname as fullname
FROM users;
为生成的列创建索引
您还可以为生成的列编制索引,或将其用作外键。
为了帮助查询我们 FullName
生成的列,我们可以创建二级索引,如以下代码段所示。
GoogleSQL
CREATE INDEX UsersByFullName ON Users (FullName);
PostgreSQL
CREATE INDEX UserByFullName ON users (fullname);
向现有表添加生成的列
使用以下 ALTER TABLE
语句,我们可以将生成的列添加到 Users
表中以生成并存储用户名的首写字母。
GoogleSQL
ALTER TABLE Users ADD COLUMN Initials STRING(2)
AS (ARRAY_TO_STRING([SUBSTR(FirstName, 0, 1), SUBSTR(LastName, 0, 1)], "")) STORED;
PostgreSQL
ALTER TABLE users ADD COLUMN initials VARCHAR(2)
GENERATED ALWAYS AS (SUBSTR(firstname, 0, 1) || SUBSTR(lastname, 0, 1)) STORED;
如果您将存储的生成列添加到现有表中,系统会启动一个长时间运行的操作来回填列值。回填期间,无法读取或查询存储的生成列。回填状态会反映在 INFORMATION_SCHEMA 表中。
使用生成的列创建部分索引
如果我们只想查询 18 岁以上的用户,该怎么办?对表进行完整扫描的效率很低,因此我们使用部分索引。
使用以下语句添加另一个生成的列,如果用户年龄超过 18 岁,将返回用户年龄;否则返回
NULL
。GoogleSQL
ALTER TABLE Users ADD COLUMN AgeAbove18 INT64 AS (IF(Age > 18, Age, NULL));
PostgreSQL
ALTER TABLE Users ADD COLUMN AgeAbove18 BIGINT GENERATED ALWAYS AS (nullif( Age , least( 18, Age) )) VIRTUAL;
在该新列中创建索引,并停用将带
NULL_FILTERED
关键字的NULL
值编入索引(在 GoogleSQL 中)或将带IS NOT NULL
谓词的NULL
值编入索引(在 PostgreSQL 中)。部分索引比普通索引更小、更高效,因为它排除了年满 18 周岁或不足 18 周岁的所有人。GoogleSQL
CREATE NULL_FILTERED INDEX UsersAbove18ByAge ON Users (AgeAbove18);
PostgreSQL
CREATE INDEX UsersAbove18ByAge ON users (AgeAbove18) WHERE AgeAbove18 IS NOT NULL;
如需检索所有 18 岁以上的用户的
Id
和Age
,请运行以下查询。GoogleSQL
SELECT Id, Age FROM Users@{FORCE_INDEX=UsersAbove18ByAge} WHERE AgeAbove18 IS NOT NULL;
PostgreSQL
SELECT Id, Age FROM users /*@ FORCE_INDEX = UsersAbove18ByAge */ WHERE AgeAbove18 IS NOT NULL;
如需按其他年龄过滤用户(例如,检索年龄超过 21 岁的所有用户),请对生成的列使用同一索引和过滤条件,如下所示:
GoogleSQL
SELECT Id, Age FROM Users@{FORCE_INDEX=UsersAbove18ByAge} WHERE AgeAbove18 > 21;
PostgreSQL
SELECT Id, Age FROM users /*@ FORCE_INDEX = UsersAbove18ByAge */ WHERE AgeAbove18 > 21;
与
STORED
生成的列相比,编入索引的生成列可以节省查询时对表达式求值的费用,并避免将值存储两次(在基表和索引中)。
移除生成的列
以下 DDL 语句会从 Users
表中删除生成列:
GoogleSQL
ALTER TABLE Users DROP COLUMN Initials;
PostgreSQL
ALTER TABLE users DROP COLUMN initials;
修改生成的列表达式
GoogleSQL
ALTER TABLE Users ALTER COLUMN FullName STRING(100)
AS (ARRAY_TO_STRING(ARRAY_TO_STRING([LastName, FirstName ], " ")));
PostgreSQL
ALTER TABLE users ADD COLUMN Initials VARCHAR(2)
GENERATED ALWAYS AS (lastname || ' ' || firstname) VIRTUAL;
不允许更新 STORED
生成的列或编入索引的非存储生成的列的表达式。
为生成的列创建主键
在 Spanner 中,您可以在主键中使用 STORED
生成的列。
以下示例展示了一个 DDL 语句,该语句用于创建包含 ShardId
生成列的 UserInfoLog
表。ShardId
列的值取决于另一列。它是通过对 UserId
列使用 MOD
函数派生的。ShardId
被声明为主键的一部分。
GoogleSQL
CREATE TABLE UserInfoLog (
ShardId INT64 NOT NULL
AS (MOD(UserId, 2048)) STORED,
UserId INT64 NOT NULL,
FullName STRING(1024) NOT NULL,
) PRIMARY KEY (ShardId, UserId);
PostgreSQL
CREATE TABLE UserInfoLog (
ShardId BIGINT GENERATED ALWAYS
AS (MOD(UserId, '2048'::BIGINT)) STORED NOT NULL,
UserId BIGINT NOT NULL,
FullName VARCHAR(1024) NOT NULL,
PRIMARY KEY(ShardId, UserId));
通常,如需高效访问特定行,您需要指定所有关键列。在前面的示例中,这意味着同时提供 ShardId
和 UserId
。不过,如果生成的主键列依赖于单个其他列,并且所依赖列的值已完全确定,Spanner 有时可以推断出该主键列的值。如果生成的主键列引用的列满足以下任一条件,则为真:
- 它等于
WHERE
子句中的常量值或绑定参数,或者 - 其值由
WHERE
子句中的IN
运算符设置 - 它从等式联接条件中获取值
例如,对于以下查询:
GoogleSQL
SELECT * FROM UserInfoLog
AS T WHERE T.UserId=1;
PostgreSQL
SELECT * FROM UserInfoLog
AS T WHERE T.UserId=1;
Spanner 可以从提供的 UserId
推断出 ShardId
的值。上述查询在经过查询优化后等效于以下查询:
GoogleSQL
SELECT * FROM UserInfoLog
AS T WHERE T.ShardId = MOD(1, 2048)
AND T.UserId=1;
PostgreSQL
SELECT * FROM UserInfoLog
AS T WHERE T.ShardId = MOD(1, 2048)
AND T.UserId=1;
以下示例展示了如何创建 Students
表,以及如何使用一个表达式检索 StudentInfo
JSON 列的 id
字段并将其用作主键:
GoogleSQL
CREATE TABLE Students (
StudentId INT64 NOT NULL
AS (CAST(JSON_VALUE(StudentInfo, "$.id") AS INT64)) STORED,
StudentInfo JSON NOT NULL,
) PRIMARY KEY (StudentId);
PostgreSQL
CREATE TABLE Students (
StudentId BIGINT GENERATED ALWAYS
AS (((StudentInfo ->> 'id'::TEXT))::BIGINT) STORED NOT NULL,
StudentInfo JSONB NOT NULL,
PRIMARY KEY(StudentId));
查看生成的列的属性
Spanner 的 INFORMATION_SCHEMA
包含有关数据库中的生成的列的信息。以下示例介绍了您可以通过查询信息架构回答的一些问题。
我的数据库中定义了哪些生成的列?
GoogleSQL
SELECT c.TABLE_NAME, c.COLUMN_NAME, C.IS_STORED
FROM INFORMATION_SCHEMA.COLUMNS as c
WHERE c.GENERATION_EXPRESSION IS NOT NULL;
PostgreSQL
SELECT c.TABLE_NAME, c.COLUMN_NAME, C.IS_STORED
FROM INFORMATION_SCHEMA.COLUMNS as c
WHERE c.GENERATION_EXPRESSION IS NOT NULL;
对于存储的生成列,IS_STORED
为 YES
;对于非存储的生成列,IS_STORED
为 NO
;对于非生成的列,IS_STORED
为 NULL
。
表 Users
中生成的列的当前状态如何?
如果您向现有表添加了生成的列,则可能需要在查询中传递 SPANNER_STATE
以了解该列的当前状态。SPANNER_STATE
会返回以下值:
COMMITTED
:该列完全可用。WRITE_ONLY
:正在回填列。不允许读取。
使用以下查询查找列的状态:
GoogleSQL
SELECT c.TABLE_NAME, c.COLUMN_NAME, c.SPANNER_STATE
FROM INFORMATION_SCHEMA.COLUMNS AS c
WHERE c.TABLE_NAME="Users" AND c.GENERATION_EXPRESSION IS NOT NULL;
PostgreSQL
SELECT c.TABLE_NAME, c.COLUMN_NAME, c.SPANNER_STATE
FROM INFORMATION_SCHEMA.COLUMNS AS c
WHERE c.TABLE_NAME='users' AND c.GENERATION_EXPRESSION IS NOT NULL;
注意:非存储的生成列只能使用 SQL 查询访问。不过,如果它已编入索引,您可以使用读取 API 从索引中访问该值。
性能
STORED
生成的列不会影响读取或查询操作的性能。不过,由于评估生成的列表达式的开销,查询中使用的非存储生成的列可能会影响其性能。
使用 STORED
生成的列或编入索引的生成的列时,写入操作(DML 语句和更改)的性能会受到影响。开销是由于在写入操作插入或修改生成的列表达式中引用的任何列时,需要评估生成的列表达式。由于开销因应用的写入工作负载、架构设计和数据集特性而异,因此我们建议您在使用生成的列之前对应用进行基准测试。
后续步骤
详细了解 Spanner 的 GoogleSQL 方言数据库的信息架构和 PostgreSQL 方言数据库的信息架构。
如需详细了解生成的列,请参阅 CREATE TABLE 参数详细信息。