创建和管理生成的列

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

向新表添加生成的列

在以下 CREATE TABLE 代码段中,我们将创建一个表来存储用户的相关信息。我们有 FirstNameLastName 的列,并为 FullName(它由 FirstNameLastName 串联而成)定义了一个生成的列。

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 岁以上的用户的 IdAge,请运行以下查询。

    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 参数详细信息。