Generierte Spalten erstellen und verwalten

Eine generierte Spalte ist eine Spalte, die immer aus anderen Spalten in einer Zeile berechnet wird. Diese Spalten können eine Abfrage vereinfachen, die Kosten für die Auswertung eines Ausdrucks zur Abfragezeit sparen und indiziert oder als Fremdschlüssel verwendet werden. In diesem Artikel wird beschrieben, wie Sie diesen Spaltentyp in Ihrer Datenbank verwalten.

Generierte Spalte zu einer neuen Tabelle hinzufügen

Im folgenden CREATE TABLE-Snippet erstellen wir eine Tabelle, in der Informationen zu Nutzern gespeichert werden. Wir haben Spalten für FirstName und LastName und definieren eine generierte Spalte für FullName, die Verkettung von FirstName und 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)
);

Der Wert von FullName wird berechnet, wenn eine neue Zeile eingefügt wird oder wenn FirstName und/oder LastName für eine vorhandene Zeile aktualisiert werden. Der berechnete Wert wird zusammen mit den anderen Spalten aus der Tabelle gespeichert. Der SQL-Code in Klammern wird als Generierungsausdruck bezeichnet.

  • expression kann ein beliebiger gültiger SQL-Ausdruck sein, der dem Datentyp der Spalte mit den folgenden Einschränkungen zugewiesen werden kann.

  • Das Attribut STORED, das dem Ausdruck folgt, speichert das Ergebnis des Ausdrucks zusammen mit anderen Spalten der Tabelle. Nachträgliche Aktualisierungen jeder der Spalten, auf die verwiesen wird, führen dazu, dass Spanner den Ausdruck neu bewertet und speichert.

  • Spanner erlaubt nur dann generierte Spalten, wenn das Attribut STORED verwendet wird.

  • Direkte Schreibvorgänge in generierten Spalten sind nicht zulässig.

  • Die Spaltenoption allow_commit_timestamp ist für generierte Spalten oder Spalten, auf die Spalten verweisen, nicht zulässig.

  • Sie können den Datentyp einer generierten Spalte oder Spalten, auf die die generierte Spalte verweist, nicht ändern.

  • Spalten, auf die in einer generierten Spalte verwiesen wird, können nicht gelöscht werden.

  • Sie können eine generierte Spalte als Primärschlüssel mit den folgenden zusätzlichen Einschränkungen verwenden:

    • Der generierte Primärschlüssel kann nicht auf andere generierte Spalten verweisen.

    • Der generierte Primärschlüssel kann höchstens auf eine Nicht-Schlüsselspalte verweisen.

    • Der generierte Primärschlüssel darf nicht von einer Nicht-Schlüsselspalte mit einer DEFAULT-Klausel abhängen.

  • Bei der Verwendung von generierten Schlüsselspalten gelten die folgenden Regeln:

    • APIs lesen: Sie müssen die Schlüsselspalten einschließlich der generierten Schlüsselspalten vollständig angeben.
    • Mutation APIs: Für INSERT, INSERT_OR_UPDATE und REPLACE ist es in Spanner nicht möglich, generierte Schlüsselspalten anzugeben. Für UPDATE können Sie optional generierte Schlüsselspalten angeben. Für DELETE müssen Sie die Schlüsselspalten einschließlich der generierten Schlüssel vollständig angeben.
    • DML: In INSERT- oder UPDATE-Anweisungen können Sie nicht explizit auf generierte Schlüssel schreiben.
    • Abfrage: Im Allgemeinen empfehlen wir, die generierte Schlüsselspalte als Filter in Ihrer Abfrage zu verwenden. Optional: Wenn der Ausdruck für die generierte Schlüsselspalte nur eine Spalte als Referenz verwendet, kann die Abfrage eine Bedingung (=) oder IN auf die referenzierte Spalte anwenden. Weitere Informationen und ein Beispiel finden Sie unter Eindeutigen Schlüssel aus einer Wertspalte erstellen.

Die erzeugte Spalte kann wie jede andere Spalte abgefragt werden, wie im folgenden Beispiel gezeigt.

GoogleSQL 

SELECT Id, FullName
FROM Users;

PostgreSQL 

SELECT id, fullname
FROM users;

Dies entspricht der folgenden Anweisung, in der die gespeicherte generierte Spalte nicht verwendet wird.

GoogleSQL 

SELECT Id, ARRAY_TO_STRING([FirstName, LastName], " ") as FullName
FROM Users;

PostgreSQL 

SELECT id, firstname || ' ' || lastname as fullname
FROM users;

Neben dem Erstellen der Abfrage und der Kosteneinsparung des Ausdrucks bei der Abfrage kann eine generierte Spalte auch indexiert oder als Fremdschlüssel verwendet werden.

Index für eine generierte Spalte erstellen

Um Suchvorgänge in unserer generierten FullName-Spalte zu unterstützen, können wir einen sekundären Index erstellen, wie im folgenden Snippet gezeigt.

GoogleSQL 

CREATE INDEX UsersByFullName ON Users (FullName);

PostgreSQL 

CREATE INDEX UserByFullName ON users (fullname);

Generierte Spalte zu einer vorhandenen Tabelle hinzufügen

Mit der folgenden ALTER TABLE-Anweisung fügen wir der Tabelle Users eine generierte Spalte hinzu, um die Initialen des Nutzers zu generieren und zu speichern.

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;

Das Hinzufügen einer gespeicherten generierten Spalte zu einer vorhandenen Tabelle ist ein lang andauernder Vorgang zum Backfill der Spaltenwerte. Während des Backfills können die gespeicherten generierten Spalten nicht gelesen oder abgefragt werden. Der Backfill-Status wird in INFORMATION_SCHEMA angezeigt.

Teilindex mit einer generierten Spalte erstellen

Was wäre, wenn wir nur Nutzer abfragen möchten, die mindestens 18 Jahre alt sind? Ein vollständiger Scan der Tabelle wäre ineffizient, daher verwenden wir einen Teilindex.

  1. Verwenden Sie die folgende Anweisung, um eine weitere generierte Spalte hinzuzufügen. Diese gibt das Alter des Nutzers zurück, wenn er über 18 Jahre alt ist. Andernfalls wird NULL zurückgegeben.

    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. Erstellen Sie einen Index für diese neue Spalte und deaktivieren Sie die Indexierung von NULL-Werten mit dem Schlüsselwort NULL_FILTERED in Google SQL oder dem Prädikat IS NOT NULL in PostgreSQL. Dieser Teilindex ist kleiner und effizienter als ein normaler Index, da dabei alle Personen ausgeschlossen werden, die unter 18 Jahre alt sind.

    GoogleSQL 

    CREATE NULL_FILTERED INDEX UsersAbove18ByAge
ON Users (AgeAbove18);

    PostgreSQL 

    CREATE INDEX UsersAbove18ByAge ON users (AgeAbove18)
WHERE AgeAbove18 IS NOT NULL;

  3. Führen Sie die folgende Abfrage aus, um Id und Age aller Nutzer über 18 abzurufen.

    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. Wenn Sie beispielsweise nach einem anderen Alter filtern möchten, rufen Sie den gleichen Index und Filter für die generierte Spalte auf, um beispielsweise alle Nutzer abzurufen, die über 21 Jahre alt sind:

    GoogleSQL 

    SELECT Id, Age
FROM Users@{FORCE_INDEX=UsersAbove18ByAge}
WHERE AgeAbove18 > 21;

    PostgreSQL 

    SELECT Id, Age
FROM users /*@ FORCE_INDEX = UsersAbove18ByAge */
WHERE AgeAbove18 > 21;

Generierte Spalte entfernen

Mit der folgenden DDL-Anweisung wird eine generierte Spalte aus der Tabelle Users gelöscht.

ALTER TABLE Users
DROP COLUMN Initials;

Generierten Spaltenausdruck ändern

Der Ausdruck einer generierten Spalte kann nicht geändert werden. Stattdessen müssen Sie die vorhandene Spalte löschen und eine neue generierte Spalte mit dem neuen Ausdruck erstellen.

Primärschlüssel in einer generierten Spalte erstellen

In Spanner können Sie im Primärschlüssel eine generierte Spalte verwenden.

Das folgende Beispiel zeigt eine DDL-Anweisung, mit der die Tabelle UserInfoLog mit einer von ShardId generierten Spalte erstellt wird. Der Wert der Spalte ShardId hängt von einer anderen Spalte ab. Es wird mithilfe einer MOD-Funktion aus der Spalte UserId abgeleitet. ShardId ist als Teil des Primärschlüssels deklariert.

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

Normalerweise müssen Sie für den effizienten Zugriff auf eine bestimmte Zeile alle Schlüsselspalten angeben. Im vorherigen Beispiel würde dies bedeuten, dass sowohl ein ShardId als auch ein UserId angegeben werden. Spanner kann jedoch manchmal den Wert der generierten Primärschlüsselspalte ableiten, wenn er von einer einzelnen anderen Spalte abhängt und der Wert der Spalte, von der er abhängt, vollständig bestimmt ist. Dies gilt, wenn die Spalte, auf die von der generierten Primärschlüsselspalte verwiesen wird, eine der folgenden Bedingungen erfüllt:

  • Sie entspricht einem konstanten Wert oder gebundenen Parameter in der WHERE-Klausel.
  • Der Wert wird von einem IN-Operator in der WHERE-Klausel festgelegt
  • Es erhält seinen Wert aus einer equi-join-Bedingung

Beispiel für die folgende Abfrage:

GoogleSQL 

SELECT * FROM UserInfoLog
AS T WHERE T.UserId=1;

PostgreSQL 

SELECT * FROM UserInfoLog
AS T WHERE T.UserId=1;

Spanner kann den Wert von ShardId aus dem bereitgestellten UserId ableiten. Die vorherige Abfrage entspricht nach der Abfrageoptimierung der folgenden Abfrage:

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;

Im nächsten Beispiel wird gezeigt, wie Sie die Tabelle Students erstellen und einen Ausdruck verwenden, der das Feld id der JSON-Spalte StudentInfo abruft und ihn als Primärschlüssel verwendet:

GoogleSQL 

CREATE TABLE Students (
  StudentId INT64 NOT NULL
  AS (CAST(JSON_VALUE(StudentInfo, "$.id") AS INT64)) STORED,
  StudentInfo JSON NOT NULL,
) PRIMARY KEY (StudentId);

PostgreSQL 

CREATE TABLE Students (
  StudentId BIGINT GENERATED ALWAYS
  AS (((StudentInfo ->> 'id'::TEXT))::BIGINT) STORED NOT NULL,
  StudentInfo JSONB NOT NULL,
  PRIMARY KEY(StudentId));

Eigenschaften einer generierten Spalte ansehen

Die INFORMATION_SCHEMA von Spanner enthält Informationen zu den generierten Spalten in Ihrer Datenbank. Im Folgenden finden Sie einige Beispiele für Fragen, die Sie beim Abfragen des Informationsschemas beantworten können.

Welche generierten Spalten werden in meiner Datenbank definiert?

SELECT c.TABLE_NAME, c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS as c
WHERE c.GENERATION_EXPRESSION IS NOT NULL;

Wie ist der aktuelle Status der generierten Spalten in der Tabelle Users?

Wenn Sie eine generierte Spalte zu einer vorhandenen Tabelle hinzugefügt haben, können Sie SPANNER_STATE in einer Abfrage übergeben, um den aktuellen Status der Spalte zu ermitteln. SPANNER_STATE gibt die folgenden Werte zurück:

  • COMMITTED: Die Spalte ist vollständig nutzbar.
  • WRITE_ONLY: Backfill für diese Spalte wird durchgeführt. Es ist kein Lesevorgang erlaubt.

Verwenden Sie die folgende Abfrage, um den Status einer Spalte zu ermitteln:

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;

Hinweis: Eine generierte Spalte wirkt sich nicht auf die Leistung eines Lese- oder Abfragevorgangs aus. Es kann sich jedoch auf die Leistung von Schreibvorgängen („DML“-Anweisungen und „Mutations“) auswirken, da der Spaltenausdruck einer generierten Spalte geändert wird, wenn durch einen Schreibvorgang eine der im generierten Spaltenausdruck referenzierten Spalten geändert wird. Da der Aufwand je nach Schreibarbeit für die Anwendung, das Schemadesign und die Dataset-Eigenschaften variiert, sollten Sie Ihre Anwendungen vergleichen, bevor Sie eine generierte Spalte verwenden.

Nächste Schritte

  • Weitere Informationen zum INFORMATION-SCHEMA von Spanner.

  • Weitere Informationen zu generierten Spalten finden Sie in den Parameterdetails von CREATE TABLE.