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 einer neuen Tabelle hinzufügen

Im folgenden CREATE TABLE-Snippet wird eine Tabelle erstellt, in der Informationen zu Nutzern gespeichert werden. Sie haben Spalten für FirstName und LastName und definieren eine generierte Spalte für FullName. Dies ist die Verkettung von FirstName und LastName.

Google-Standard-SQL 

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 nach dem Ausdruck bewirkt, dass das Ergebnis der Funktion zusammen mit anderen Spalten der Tabelle gespeichert wird. Bei späteren Aktualisierungen einer der referenzierten Spalten wird der Ausdruck noch einmal ausgewertet und gespeichert.

  • Generierte Spalten ohne das Attribut STORED sind nicht zulässig.

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

  • Generierte Spalten können nicht als Primärschlüssel für einen Teil oder als Teil davon verwendet werden. Sie können jedoch sekundäre Indexschlüssel sein.

  • Die Spaltenoption allow_commit_timestamp ist in generierten Spalten oder Spalten, auf die von generierten Spalten verwiesen wird, nicht zulässig.

  • Sie können den Datentyp einer generierten Spalte oder von der generierten Spalte referenzierte Spalten nicht ändern.

  • Sie können eine Spalte nicht löschen, auf die von einer generierten Spalte verwiesen wird.

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

Google-Standard-SQL 

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.

Google-Standard-SQL 

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.

Google-Standard-SQL 

CREATE INDEX UsersByFullName ON Users (FullName);

PostgreSQL 

CREATE INDEX UserByFullName ON users (fullname);

Generierte Spalte 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.

Google-Standard-SQL 

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 Vorgang mit langer Ausführungszeit zum Backfill der Spaltenwerte. Beim Backfill können die gespeicherten generierten Spalten weder gelesen noch abgefragt werden. Der Backfill-Status wird im 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. Mit der folgenden Anweisung können Sie eine weitere generierte Spalte hinzufügen, die das Alter des Nutzers zurückgibt, wenn das Alter über 18 Jahre liegt. Andernfalls wird NULL zurückgegeben.

    Google-Standard-SQL 

    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 NULL_FILTERED-Keyword in Google Standard SQL oder dem IS NOT NULL-Prädikat in PostgreSQL. Dieser Teilindex ist kleiner und effizienter als ein normaler Index, da er alle Nutzer von 18 Jahren oder jünger ausschließt.

    Google-Standard-SQL 

    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.

    Google-Standard-SQL 

    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. Um beispielsweise nach einem anderen Alter zu filtern und alle Nutzer ab 21 Jahren abzurufen, verwenden Sie denselben Index und filtern Sie die generierte Spalte so:

    Google-Standard-SQL 

    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 entfernen und eine neue generierte Spalte mit dem neuen Ausdruck erstellen.

Eigenschaften einer generierten Spalte aufrufen

INFORMATION_SCHEMA von Cloud Spanner enthält Informationen zu den generierten Spalten. Im Folgenden finden Sie einige Beispiele für Fragen, die Sie durch 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 einer vorhandenen Tabelle eine generierte Spalte hinzugefügt haben, möchten Sie möglicherweise deren aktuellen Status anzeigen, um beispielsweise festzustellen, ob die Spalte aufgefüllt und vollständig verwendbar ist. Wenn SPANNER_STATE in der folgenden Abfrage WRITE_ONLY zurückgibt, ist Cloud Spanner noch dabei, die Spalte zu füllen und es sind keine Lesevorgänge erlaubt.

Google-Standard-SQL 

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;

Weitere Informationen