建立及管理產生的資料欄

系統產生的資料欄是指一律從資料列中的其他資料欄計算而得的資料欄。這些資料欄可簡化查詢，節省在查詢時評估運算式的成本，並可建立索引或用做外鍵。本頁面說明如何在 GoogleSQL 方言資料庫和 PostgreSQL 方言資料庫中管理這個資料庫的欄位類型。

將產生的資料欄新增至新資料表

在以下 CREATE TABLE 程式碼片段中，我們會建立表格來儲存使用者相關資訊。我們有 FirstName 和 LastName 的資料欄，並定義 FullName 的產生資料欄，也就是 FirstName 和 LastName 的串接。括號中的 SQL 稱為產生運算式。

您可以將產生的資料欄標示為 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：您必須完整指定索引鍵欄，包括產生的索引鍵欄。
    • Mutation 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. 在這個新欄上建立索引，並在 GoogleSQL 中使用 NULL_FILTERED 關鍵字或 PostgreSQL 中的 IS NOT NULL 判定式，停用 NULL 值的索引。這個部分索引比一般索引更小，且效率更高，因為它會排除年齡在 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;

    與 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 (INT64(StudentInfo.id)) STORED,
  StudentInfo JSON NOT NULL,
) PRIMARY KEY (StudentId);

PostgreSQL

CREATE TABLE Students (
  StudentId BIGINT GENERATED ALWAYS
  AS ((StudentInfo ->> 'id')::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 產生的資料欄不會影響讀取或查詢作業的效能。不過，由於評估產生資料欄運算式的額外負擔，在查詢中使用的非儲存產生資料欄可能會影響查詢效能。

使用 STORED 產生的資料欄或已編入索引的產生資料欄時，寫入作業 (DML 陳述式和變異) 的效能會受到影響。這項額外負擔是因為在寫入作業插入或修改產生資料欄運算式中參照的任何資料欄時，會評估產生的資料欄運算式。由於開銷會因應用程式的寫入工作負載、結構定義設計和資料集特性而異，建議您在使用產生的資料欄前，先對應用程式進行基準測試。

後續步驟