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 wird eine Tabelle zum Speichern von Informationen über Nutzer erstellt. Wir haben Spalten für FirstName und LastName und definieren eine generierte Spalte für FullName, die die Verkettung von FirstName und LastName darstellt.

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

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 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 für generierte Spalten und für Spalten, die von generierten Spalten referenziert werden, 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.

SELECT Id, FullName
FROM Users;

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

SELECT Id, ARRAY_TO_STRING([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.

CREATE INDEX UsersByFullName ON Users (FullName);

Generierte Spalte zu einer vorhandenen Tabelle hinzufügen

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

ALTER TABLE Users ADD COLUMN Initials STRING(2)
AS (ARRAY_TO_STRING([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 in INFORMATION_SCHEMA wiedergegeben.

Teilindex mithilfe 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.

    ALTER TABLE Users ADD COLUMN AgeAbove18 INT64
AS (IF(Age > 18, Age, NULL)) STORED;

  2. Erstellen Sie für diese neue Spalte einen Index und deaktivieren Sie die Indexierung von NULL-Werten mit dem Schlüsselwort NULL_FILTERED. Dieser Teilindex ist kleiner und effizienter als ein normaler Index, da er alle Nutzer von 18 Jahren oder jünger ausschließt.

    CREATE NULL_FILTERED INDEX UsersAbove18ByAge
ON Users (AgeAbove18);

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

    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:

    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.

Attribute einer generierten Spalte ansehen

Das INFORMATION_SCHEMA von Cloud Spanner enthält Informationen zu den generierten Spalten in Ihrer Datenbank. 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.

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;

Nächste Schritte