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.
Der Ausdruck kann nur auf Spalten in derselben Tabelle verweisen.
Der Ausdruck darf keine Unterabfragen enthalten.
Der Ausdruck darf keine nicht deterministischen Funktionen wie
PENDING_COMMIT_TIMESTAMP()
,CURRENT_DATE()
undCURRENT_TIMESTAMP()
enthalten.Sie können den Ausdruck einer generierten Spalte nicht ändern.
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.
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;
Erstellen Sie für diese neue Spalte einen Index und deaktivieren Sie die Indexierung von
NULL
-Werten mit dem SchlüsselwortNULL_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);
Führen Sie die folgende Abfrage aus, um
Id
undAge
aller Nutzer über 18 abzurufen.SELECT Id, Age FROM Users@{FORCE_INDEX=UsersAbove18ByAge} WHERE AgeAbove18 IS NOT NULL;
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
Weitere Informationen zum INFORMATION SCHEMA von Cloud Spanner.
Weitere Informationen zu generierten Spalten finden Sie in den Parameterdetails zu TABELLE ERSTELLEN.