建立及管理產生的資料欄

系統產生的資料欄一律會根據資料列中的其他資料欄計算得出。這些資料欄可簡化查詢、節省查詢時評估運算式的成本，並可建立索引或做為外部鍵。本頁面說明如何管理 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 或已建立索引的產生資料欄運算式。

  • 如果是 GoogleSQL 方言資料庫，類型為 STRING 或 BYTES 的非儲存產生資料欄長度必須為 MAX。

  • 如果是 PostgreSQL 方言資料庫，類型為 VARCHAR 的非儲存或虛擬產生資料欄，長度必須為 MAX。

  • 運算式後方的 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. 在這個新資料欄上建立索引，並在 GoogleSQL 中使用 NULL NULL_FILTERED 關鍵字，或在 PostgreSQL 中使用 IS NOT 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 陳述式會建立 UserInfoLog 資料表，其中包含 ShardId 產生的資料欄。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 有時可以推斷該值。如果產生的主鍵資料欄所參照的資料欄符合下列任一條件，即為 True：

  • 等於 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 陳述式和變異) 的效能會受到影響。當寫入作業插入或修改所參照的任何資料欄時，系統會評估產生的資料欄運算式，因此會產生額外負擔。由於額外負荷會因應用程式的寫入工作負載、結構定義設計和資料集特徵而異，建議您先對應用程式進行基準測試，再使用產生的資料欄。

後續步驟