创建和管理生成的列

生成的列始终是由某行中的其他列计算得出的列。这些列可简化查询、查询时评估表达式的费用，并可编织索引或作为外键。在本文中，我们将介绍如何在数据库中管理此列类型。

向新表添加生成的列

在以下 CREATE TABLE 代码段中，我们创建一个用于存储信息的表 用户信息我们设置了 FirstName 和 LastName 列，并定义了 为 FullName 生成的列，该列是 FirstName 和 LastName.括号中的 SQL 称为生成表达式。

可以将生成的列标记为 STORED，以节省评估 表达式。因此，仅计算 FullName 的值 插入新行或者更新 FirstName 或 LastName 时触发 现有行。计算值与 表格。

GoogleSQL

CREATE TABLE Users (
Id STRING(20) NOT NULL,
FirstName STRING(50),
LastName STRING(50),
Age INT64 NOT NULL,
FullName STRING(100) AS (ARRAY_TO_STRING([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)
);

您可以通过省略 STORED 来创建非存储生成的列 属性。系统会在查询时对生成的这类列进行求值 并能简化查询在 PostgreSQL 中，您可以创建 使用 VIRTUAL 属性生成列。

GoogleSQL

FullName STRING(MAX) AS (CONCAT(FirstName, " ", LastName))

PostgreSQL

fullname text GENERATED ALWAYS AS (firstname || ' ' || lastname) VIRTUAL

  • expression 可以是任意有效的 SQL 表达式， 可分配给列数据类型，但存在以下限制。

  • 表达式后面的 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 岁以上的用户，该怎么办？对表进行完整扫描的效率很低，因此我们使用部分索引。

  1. 使用以下语句添加另一个生成的列，该列会返回 如果用户已年满 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;

  2. 对此新列创建索引，并禁止将 NULL 编入索引 将值与 NULL_FILTERED 关键字（在 GoogleSQL 中）或 IS NOT NULL 谓词（在 PostgreSQL 中）搭配使用。此部分索引较小 而且比普通索引更高效，因为它排除了 不超过 18 周岁。

    GoogleSQL

    CREATE NULL_FILTERED INDEX UsersAbove18ByAge
ON Users (AgeAbove18);

    PostgreSQL

    CREATE INDEX UsersAbove18ByAge ON users (AgeAbove18)
WHERE AgeAbove18 IS NOT NULL;

  3. 如需检索所有 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;

  4. 按其他年龄进行过滤，例如，检索 对生成的列使用相同的索引和过滤条件，如下所示：

    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 主键。

以下示例展示了用于创建 UserInfoLog 的 DDL 语句 表格，其中包含 ShardId 生成的列。ShardId 列的值 取决于另一列。它通过对MOD UserId 列。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；对于非存储列，为 NO 生成的列或 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 生成的列不会影响读取或查询的性能 操作。但是，查询中使用的非存储生成的列可能会影响其 因为评估生成的列表达式会产生开销。

写入操作（DML 语句和变更）的性能受到影响 使用生成的列（使用 STORED 生成的列）或 编入索引。开销是在计算生成的列表达式时计算 写入操作会插入或修改 生成的列表达式。由于开销因写入操作而异 应用工作负载、架构设计和数据集特征，我们 建议您在使用生成的列之前先对应用进行基准测试。

后续步骤