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 in eine neue Tabelle einfügen
Im folgenden CREATE TABLE
-Snippet wird eine Tabelle erstellt, um Informationen zu Nutzern zu speichern. Wir haben Spalten für FirstName
und LastName
und definieren eine generierte Spalte für FullName
, die Verkettung von FirstName
und LastName
. Der SQL-Code in Klammern wird als Generierungsausdruck bezeichnet.
Eine generierte Spalte kann als STORED
gekennzeichnet werden, um die Kosten für die Auswertung des Ausdrucks zum Zeitpunkt der Abfrage zu sparen. Daher wird der Wert von FullName
nur berechnet, wenn eine neue Zeile eingefügt oder FirstName
oder LastName
für eine vorhandene Zeile aktualisiert wird. Der berechnete Wert wird zusammen mit anderen Spalten in der Tabelle gespeichert.
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)
);
Sie können eine nicht gespeicherte generierte Spalte erstellen, indem Sie das Attribut STORED
in der DDL weglassen. Diese Art von erzeugter Spalte wird zum Zeitpunkt der Abfrage ausgewertet und kann die Abfrage vereinfachen. In PostgreSQL können Sie mit dem Attribut VIRTUAL
eine nicht gespeicherte generierte Spalte erstellen.
GoogleSQL
FullName STRING(MAX) AS (CONCAT(FirstName, " ", LastName))
PostgreSQL
fullname text GENERATED ALWAYS AS (firstname || ' ' || lastname) VIRTUAL
expression
kann ein beliebiger gültiger SQL-Ausdruck sein, der dem Spaltendatentyp mit den folgenden Einschränkungen zugewiesen werden kann.Der Ausdruck kann nur auf Spalten in derselben Tabelle verweisen.
Der Ausdruck darf keine Unterabfragen enthalten.
Ausdrücke mit nicht deterministischen Funktionen wie
PENDING_COMMIT_TIMESTAMP()
,CURRENT_DATE()
undCURRENT_TIMESTAMP()
können nicht in eine mitSTORED
generierte oder indexierte Spalten umgewandelt werden.Sie können den Ausdruck einer
STORED
oder einer indexierten generierten Spalte nicht ändern.
Das Attribut
STORED
, das dem Ausdruck folgt, speichert das Ergebnis des Ausdrucks zusammen mit anderen Spalten der Tabelle. Nachfolgende Aktualisierungen an den referenzierten Spalten führen dazu, dass Spanner den Ausdruck neu bewertet und speichert.Generierte Spalten, die nicht dem Typ „
STORED
“ entsprechen, können nicht als „NOT NULL
“ markiert werden.Direkte Schreibvorgänge in generierte Spalten sind nicht zulässig.
Die Spaltenoption
allow_commit_timestamp
ist in generierten Spalten oder Spalten, die auf Spalten verweisen, nicht zulässig.Bei
STORED
oder generierten Spalten, die indexiert sind, können Sie den Datentyp der Spalte oder von Spalten, auf die die generierte Spalte verweist, nicht ändern.Sie können keine Spalte löschen, auf die eine generierte Spalte verweist.
Sie können eine generierte Spalte mit den folgenden zusätzlichen Einschränkungen als Primärschlüssel verwenden:
Der generierte Primärschlüssel darf nicht auf andere generierte Spalten verweisen.
Der generierte Primärschlüssel kann auf höchstens 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 vollständig angeben, einschließlich der generierten Schlüsselspalten.
- Mutation APIs: Für
INSERT
,INSERT_OR_UPDATE
undREPLACE
können in Spanner keine generierten Schlüsselspalten angegeben werden. FürUPDATE
können Sie optional generierte Schlüsselspalten angeben. FürDELETE
müssen Sie die Schlüsselspalten einschließlich der generierten Schlüssel vollständig angeben. - DML: In
INSERT
- oderUPDATE
-Anweisungen können Sie nicht explizit in generierte Schlüssel schreiben. - Abfrage: Im Allgemeinen empfehlen wir, die generierte Schlüsselspalte als Filter in Ihrer Abfrage zu verwenden. Wenn der Ausdruck für die generierte Schlüsselspalte nur eine Spalte als Referenz verwendet, kann die Abfrage optional eine Gleichheitsbedingung (
=
) oderIN
auf die referenzierte Spalte anwenden. Weitere Informationen und ein Beispiel finden Sie unter Aus einer Wertspalte abgeleiteten eindeutigen Schlüssel 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;
Die Abfrage mit Fullname
entspricht der Abfrage mit dem generierten Ausdruck. Daher kann eine generierte Spalte die Abfrage vereinfachen.
GoogleSQL
SELECT Id, ARRAY_TO_STRING([FirstName, LastName], " ") as FullName
FROM Users;
PostgreSQL
SELECT id, firstname || ' ' || lastname as fullname
FROM users;
Index für eine generierte Spalte erstellen
Sie können auch eine generierte Spalte indexieren oder als Fremdschlüssel verwenden.
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 können wir der Tabelle Users
eine generierte Spalte hinzufügen, 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;
Wenn Sie einer vorhandenen Tabelle eine gespeicherte generierte Spalte hinzufügen, wird ein lang andauernder Vorgang zum Backfill der Spaltenwerte gestartet. Während des Backfills können die gespeicherten generierten Spalten weder gelesen noch abgefragt werden. Der Backfill-Status wird in der Tabelle INFORMATION_SCHEMA widergespiegelt.
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.
Verwenden Sie die folgende Anweisung, um eine weitere generierte Spalte hinzuzufügen, die das Alter des Nutzers zurückgibt, 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));
PostgreSQL
ALTER TABLE Users ADD COLUMN AgeAbove18 BIGINT GENERATED ALWAYS AS (nullif( Age , least( 18, Age) )) VIRTUAL;
Erstellen Sie einen Index für diese neue Spalte und deaktivieren Sie die Indexierung von
NULL
-Werten mit dem SchlüsselwortNULL_FILTERED
in GoogleSQL oder dem PrädikatIS NOT NULL
in PostgreSQL. Dieser Teilindex ist kleiner und effizienter als ein normaler Index, da er alle Personen ausschließt, die 18 Jahre oder jünger sind.GoogleSQL
CREATE NULL_FILTERED INDEX UsersAbove18ByAge ON Users (AgeAbove18);
PostgreSQL
CREATE INDEX UsersAbove18ByAge ON users (AgeAbove18) WHERE AgeAbove18 IS NOT NULL;
Führen Sie die folgende Abfrage aus, um
Id
undAge
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;
Wenn Sie nach einem anderen Alter filtern möchten, um beispielsweise alle Nutzer abzurufen, die über 21 Jahre alt sind, verwenden Sie denselben Index und filtern Sie so nach der generierten Spalte:
GoogleSQL
SELECT Id, Age FROM Users@{FORCE_INDEX=UsersAbove18ByAge} WHERE AgeAbove18 > 21;
PostgreSQL
SELECT Id, Age FROM users /*@ FORCE_INDEX = UsersAbove18ByAge */ WHERE AgeAbove18 > 21;
Mit einer indexierten generierten Spalte können Sie die Kosten für die Auswertung eines Ausdrucks zum Zeitpunkt der Abfrage sparen und vermeiden, dass die Werte doppelt (in der Basistabelle und im Index) im Vergleich zu einer mit
STORED
generierten Spalte gespeichert werden.
Generierte Spalte entfernen
Mit der folgenden DDL-Anweisung wird eine generierte Spalte aus der Tabelle Users
gelöscht:
GoogleSQL
ALTER TABLE Users DROP COLUMN Initials;
PostgreSQL
ALTER TABLE users DROP COLUMN initials;
Generierten Spaltenausdruck ändern
GoogleSQL
ALTER TABLE Users ALTER COLUMN FullName STRING(100)
AS (ARRAY_TO_STRING(ARRAY_TO_STRING([LastName, FirstName ], " ")));
PostgreSQL
ALTER TABLE users ADD COLUMN Initials VARCHAR(2)
GENERATED ALWAYS AS (lastname || ' ' || firstname) VIRTUAL;
Der Ausdruck einer mit STORED
generierten Spalte oder einer indexierten nicht gespeicherten generierten Spalte kann nicht aktualisiert werden.
Primärschlüssel für eine generierte Spalte erstellen
In Spanner können Sie eine vom STORED
generierte Spalte im Primärschlüssel verwenden.
Das folgende Beispiel zeigt eine DDL-Anweisung, mit der die Tabelle UserInfoLog
mit einer vom ShardId
generierten Spalte erstellt wird. Der Wert der Spalte ShardId
hängt von einer anderen Spalte ab. Sie wird mithilfe einer MOD
-Funktion für die Spalte UserId
abgeleitet. ShardId
wird 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 ShardId
als auch UserId
angegeben werden. Spanner kann jedoch manchmal den Wert der generierten Primärschlüsselspalte ableiten, wenn diese von einer einzelnen anderen Spalte abhängt und der Wert der Spalte, von der sie abhängig ist, vollständig bestimmt ist. Dies ist der Fall, wenn die Spalte, auf die in 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 oder - Der Wert wird von einem
IN
-Operator in derWHERE
-Klausel festgelegt. - Der Wert stammt 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 der angegebenen UserId
ableiten.
Die vorherige Abfrage entspricht der folgenden Abfrage nach der Abfrageoptimierung:
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;
Das nächste Beispiel zeigt, wie Sie die Tabelle Students
erstellen und einen Ausdruck verwenden, der das Feld id
der JSON-Spalte StudentInfo
abruft und 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
INFORMATION_SCHEMA
von Spanner enthält Informationen zu den generierten Spalten in der Datenbank. Im Folgenden finden Sie einige Beispiele für die Fragen, die Sie beim Abfragen des Informationsschemas beantworten können.
Welche generierten Spalten werden in meiner Datenbank definiert?
GoogleSQL
SELECT c.TABLE_NAME, c.COLUMN_NAME, C.IS_STORED
FROM INFORMATION_SCHEMA.COLUMNS as c
WHERE c.GENERATION_EXPRESSION IS NOT NULL;
PostgreSQL
SELECT c.TABLE_NAME, c.COLUMN_NAME, C.IS_STORED
FROM INFORMATION_SCHEMA.COLUMNS as c
WHERE c.GENERATION_EXPRESSION IS NOT NULL;
IS_STORED
ist entweder YES
für gespeicherte generierte Spalten, NO
für nicht gespeicherte generierte Spalten oder NULL
für nicht generierte Spalten.
Wie ist der aktuelle Status der generierten Spalten in der Tabelle Users
?
Wenn Sie einer vorhandenen Tabelle eine generierte Spalte 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: Auf eine generierte Spalte, die nicht gespeichert ist, kann nur über die SQL-Abfrage zugegriffen werden. Ist er jedoch indexiert, können Sie die Lese-API verwenden, um auf den Wert aus dem Index zuzugreifen.
Leistung
Eine mit STORED
generierte Spalte wirkt sich nicht auf die Leistung eines Lese- oder Abfragevorgangs aus. Nicht gespeicherte generierte Spalten, die in einer Abfrage verwendet werden, können jedoch ihre Leistung aufgrund des Aufwands bei der Auswertung des generierten Spaltenausdrucks beeinträchtigen.
Die Leistung von Schreibvorgängen (DML-Anweisungen und -Mutationen) wird beeinträchtigt, wenn entweder eine mit STORED
generierte oder eine generierte Spalte verwendet wird, die indexiert ist. Der Aufwand ist auf die Auswertung des generierten Spaltenausdrucks zurückzuführen, wenn der Schreibvorgang eine der Spalten, auf die im generierten Spaltenausdruck verwiesen wird, einfügt oder ändert. Da der Aufwand je nach Schreibarbeitslast der Anwendung, des Schemadesigns und der Dataset-Eigenschaften variiert, sollten Sie Ihre Anwendungen messen, bevor Sie eine generierte Spalte verwenden.
Nächste Schritte
Weitere Informationen zum Informationsschema für GoogleSQL-Dialekt-Datenbanken und zum Informationsschema für PostgreSQL-Dialekt-Datenbanken von Spanner.
Weitere Informationen zu generierten Spalten finden Sie in den Details zum Parameter CREATE TABLE.