生成された列は、常に行の他の列から計算される列です。これらの列を使用すると、クエリを簡素化し、クエリの実行時に式を評価するためのコストを削減できます。また、インデックスを作成したり、外部キーとしても使用できます。この記事では、データベースでこの列の型を管理する方法について説明します。
新しいテーブルに生成された列を追加する
次の 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);
FullName の値は、新しい行が挿入されたとき、または既存の行で FirstName または LastName（あるいはその両方）が更新されたときに計算されます。計算された値は、テーブルの他の列とともに格納されます。かっこ内の SQL は生成式と呼ばれます。
式は、列のデータ型に割り当てる有効な SQL 式です。ただし、次の制限があります。
式は、同じテーブルの列のみを参照できます。
式にサブクエリを含めることはできません。
式に
PENDING_COMMIT_TIMESTAMP()、CURRENT_DATE()、CURRENT_TIMESTAMP()などの非決定性関数を含めることはできません。生成された列の式は変更できません。
式の後に
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 歳以上のユーザーのみを対象にクエリを実行する場合はどうでしょう。テーブル全体のスキャンは非効率的であるため、部分的なインデックスを使用します。
次のステートメントを使用して、生成された別の列を追加します。この列は、18 歳以上であればユーザーの年齢を返し、そうでなければ
NULLを返します。ALTER TABLE Users ADD COLUMN AgeAbove18 INT64 AS (IF(Age > 18, Age, NULL)) STORED;新しい列でインデックスを作成し、
NULL_FILTEREDキーワードを使用してNULL値のインデックス登録を無効にします。この部分的なインデックスは、18 歳未満のすべての人を除外しているため、通常のインデックスよりも小さく、効率的です。CREATE NULL_FILTERED INDEX UsersAbove18ByAge ON Users (AgeAbove18);18 歳以上のすべてのユーザーの
IdとAgeを取得するには、次のクエリを実行します。SELECT Id, Age FROM Users@{FORCE_INDEX=UsersAbove18ByAge} WHERE AgeAbove18 IS NOT NULL;たとえば 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;