创建和管理生成的列

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

向新表添加生成的列

在以下 CREATE TABLE 代码段中，我们将创建一个表来存储用户的相关信息。我们有 FirstName 和 LastName 的列，并为 FullName（它由 FirstName 与 LastName 串联而成）定义了一个生成的列。

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

在插入新行或为现有行更新 FirstName 和/或 LastName 时，计算 FullName 的值。计算值与表中的其他列一起存储。英文括号中的 SQL 称为 generation expression。

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

  • 表达式后的 STORED 属性会将函数结果与表的其他列一起存储。对任何被引用的列的后续更新都将导致对表达式重新求值和存储。

  • 不允许有无 STORED 属性的生成的列。

  • 不允许直接写入生成的列。

  • 生成的列不能用作主键或是主键的一部分。但是，它们可作为二级索引键。

  • 生成的列或生成的列引用的任何列都不允许使用列选项 allow_commit_timestamp。

  • 您无法更改已生成列的数据类型，也无法更改由生成的列引用的任何列。

  • 您无法删除由生成的列引用的列。

生成的列可以像任何其他列一样进行查询，如以下示例所示。

SELECT Id, FullName
FROM Users;

这相当于以下语句，它不使用存储的已生成列。

SELECT Id, ARRAY_TO_STRING([FirstName, LastName], " ") as FullName
FROM Users;

除了简化查询并节省查询时对表达式求值的费用外，生成的列还可以编入索引或用作外键。

为生成的列创建索引

为了帮助查询我们 FullName 生成的列，我们可以创建二级索引，如以下代码段所示。

CREATE INDEX UsersByFullName ON Users (FullName);

将生成的列添加到现有表中

使用以下 ALTER TABLE 语句，将生成的列添加到 Users 表中以生成并存储用户名的首写字母。

ALTER TABLE Users ADD COLUMN Initials STRING(2)
AS (ARRAY_TO_STRING([SUBSTR(FirstName, 0, 1), SUBSTR(LastName, 0, 1)], "")) STORED;

将存储的生成列添加到现有表的操作需要长时间运行才能回填列值。回填期间，无法读取或查询存储的列。回填状态会反映在 INFORMATION_SCHEMA 中。

使用生成的列创建部分索引

如果我们只想查询 18 岁以上的用户，该怎么办？对表进行完整扫描的效率很低，因此我们使用部分索引。

  1. 使用以下语句添加另一个生成的列，如果用户年龄超过 18 岁，将返回用户年龄；否则返回 NULL。

    ALTER TABLE Users ADD COLUMN AgeAbove18 INT64
AS (IF(Age > 18, Age, NULL)) STORED;

  2. 在该新列中创建索引，并停用将带 NULL_FILTERED 关键字的 NULL 值编入索引。部分索引比普通索引更小、更高效，因为它排除了年满 18 周岁或不足 18 周岁的所有人。

    CREATE NULL_FILTERED INDEX UsersAbove18ByAge
ON Users (AgeAbove18);

  3. 如需检索所有 18 岁以上的用户的 Id 和 Age，请运行以下查询。

    SELECT Id, Age
    FROM Users@{FORCE_INDEX=UsersAbove18ByAge}
    WHERE AgeAbove18 IS NOT NULL;

  4. 要按不同年龄过滤用户，例如，要检索超过 21 岁的所有用户，请对生成的列使用同一索引和过滤条件，如下所示：

    SELECT Id, Age
    FROM Users@{FORCE_INDEX=UsersAbove18ByAge}
    WHERE AgeAbove18 > 21;

移除生成的列

以下 DDL 语句会从 Users 表中删除生成列。

ALTER TABLE Users
DROP COLUMN Initials;

修改生成的列表达式

不允许修改生成列的表达式。您需要删除现有列并用新表达式创建新列。

查看已生成列的属性

Cloud Spanner 的 INFORMATION_SCHEMA 包含有关数据库中的生成的列的信息。以下示例介绍了您可以通过查询信息架构回答的一些问题。

我的数据库中定义了哪些生成的列？

SELECT c.TABLE_NAME, c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS as c
WHERE c.GENERATION_EXPRESSION IS NOT NULL;

表 Users 中生成的列的当前状态如何？

如果您向现有表添加了生成的列，则可能需要查看其当前状态，以确定（例如）该列是否进行了重新填充并且完全可用。如果 SPANNER_STATE 在以下查询中返回 WRITE_ONLY，则表示 Cloud Spanner 仍在回填列，并且不允许读取。

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;

后续步骤

  • 详细了解 Cloud Spanner 的 信息架构。

  • 如需详细了解生成的列，请参阅 CREATE TABLE 参数详细信息。