创建和管理生成的列

使用集合让一切井井有条 根据您的偏好保存内容并对其进行分类。

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

将生成的列添加到新表

以下 CREATE TABLE 代码段创建了一个表，用于存储用户的相关信息。我们为 FirstName 和 LastName 指定了列，并为 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)
);

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

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

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

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

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

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

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

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

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

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

GoogleSQL 

SELECT Id, FullName
FROM Users;

PostgreSQL

SELECT id, fullname
FROM users;

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

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

    PostgreSQL

    ALTER TABLE Users ADD COLUMN AgeAbove18 BIGINT
GENERATED ALWAYS AS (nullif( Age , least( 18, Age) )) STORED;

  2. 为此新列创建索引，并使用 GoogleSQL 中的 NULL_FILTERED 关键字或 PostgreSQL 中的 IS NOT NULL 谓词禁止将 NULL 值编入索引。部分索引比普通索引更小、更高效，因为它排除了年满 18 周岁或不足 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. 要按不同年龄过滤用户，例如，要检索超过 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;

移除生成的列

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

ALTER TABLE Users
DROP COLUMN Initials;

修改生成的列表达式

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

查看所生成列的属性

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，则表示 Spanner 仍在回填列，并且不允许执行读取操作。

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;

后续步骤