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. 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 bei 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 im .

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 der generierten Spalte wird zum Zeitpunkt der Abfrage ausgewertet und kann Abfragen 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 kann dem Spaltendatentyp mit den folgenden Einschränkungen zugewiesen werden.

  • 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() und CURRENT_TIMESTAMP() kann nicht in eine von STORED generierte Spalte oder in eine generierte Spalte umgewandelt werden, die indexiert.

  • Sie können den Ausdruck einer STORED oder einer indexierten generierten Spalte nicht ändern.

• Das Attribut STORED, das auf den Ausdruck folgt, speichert das Ergebnis des zusammen mit anderen Spalten der Tabelle. Bei späteren Aktualisierungen einer der referenzierten Spalten wird der Ausdruck noch einmal ausgewertet und gespeichert.

• Generierte Spalten, die keine STORED sind, können nicht als NOT NULL gekennzeichnet werden.

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

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

• Bei indexierten STORED- oder generierten Spalten können Sie den Datentyp der Spalte oder der 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 als Primärschlüssel verwenden mit: Weitere Einschränkungen:

  • Der generierte Primärschlüssel darf 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.

• Für generierte Schlüsselspalten gelten die folgenden Regeln:

  • APIs lesen: Sie müssen die Schlüsselspalten vollständig angeben, einschließlich der erzeugten Schlüsselspalten.
  • Mutation APIs: Für INSERT, INSERT_OR_UPDATE und REPLACE: Spanner lässt nicht zu, dass Sie generierte Schlüsselspalten angeben. Für UPDATE können Sie optional generierte Schlüsselspalten angeben. Für DELETE, müssen Sie die Schlüsselspalten vollständig angeben, einschließlich der erzeugten Schlüsseln.
  • DML: In INSERT- oder UPDATE-Anweisungen können keine expliziten Schreibvorgänge auf generierte Schlüssel ausgeführt werden.
  • Abfrage: Im Allgemeinen empfehlen wir, die generierte Schlüsselspalte als Filter in Ihrer Abfrage zu verwenden. Optional kann in der Abfrage, wenn der Ausdruck für die generierte Schlüsselspalte nur eine Spalte als Referenz verwendet, eine Gleichheitsbedingung (=) oder IN-Bedingung auf die referenzierte Spalte angewendet werden. Weitere Informationen und ein Beispiel: Einen eindeutigen Schlüssel erstellen, der aus einer Wertspalte abgeleitet ist

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

Wenn Sie einer vorhandenen Tabelle eine gespeicherte generierte Spalte hinzufügen, wird ein langwieriger Vorgang zum Backfill der Spaltenwerte gestartet. Beim Backfill gespeicherte generierte Spalten können nicht gelesen oder abgefragt werden. Der Backfill-Status ist in der Tabelle INFORMATION_SCHEMA angezeigt.

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.

   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;
   

2. Erstellen Sie einen Index für diese neue Spalte und deaktivieren Sie die Indexierung von NULL mit dem Schlüsselwort NULL_FILTERED in GoogleSQL oder dem Prädikat IS NOT NULL in PostgreSQL Dieser Teilindex ist kleiner und effizienter als ein normaler Index, da alle Personen 18 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;
   

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 nach einem anderen Alter filtern und beispielsweise alle Nutzer ab 21 Jahren abrufen möchten, verwenden Sie denselben Index und filtern Sie die generierte Spalte so:

   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 Ausdruck zum Zeitpunkt der Abfrage und vermeiden Sie es, die Werte zweimal zu speichern (in der Basistabelle und Index) mit einer STORED-generierten Spalte vergleichen.

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 generierten STORED-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.

Im folgenden Beispiel wird eine DDL-Anweisung gezeigt, mit der die Tabelle UserInfoLog mit einer generierten Spalte vom Typ ShardId erstellt wird. Wert der Spalte ShardId von einer anderen Spalte abhängig ist. Sie wird abgeleitet, indem eine MOD-Funktion auf der UserId. 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 alle Schlüsselspalten angeben, um effizient auf eine bestimmte Zeile zuzugreifen. Im vorherigen Beispiel würde das bedeuten, dass sowohl ShardId als auch UserId angegeben werden müssen. Manchmal kann Spanner jedoch den Wert der generierten Primärschlüsselspalte ableiten, wenn sie von einer einzelnen anderen Spalte abhängt und der Wert der abhängigen Spalte vollständig bestimmt ist. Das ist der Fall, wenn die Spalte, auf die die generierte Primärschlüsselspalte verweist, eine der folgenden Bedingungen erfüllt:

• Er entspricht einem konstanten Wert oder einem gebundenen Parameter in der WHERE-Klausel oder
• Der Wert wird von einem IN-Operator in der WHERE-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 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, mit dem das Feld id der JSON-Spalte StudentInfo abgerufen und als Primärschlüssel verwendet wird:

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 zum 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?

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 Spalten generierten 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, Übergeben Sie SPANNER_STATE in einer Abfrage, 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.

Mit der folgenden Abfrage können Sie den Status einer Spalte 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 und nicht gespeicherte Spalte kann auf die mit der SQL-Abfrage zugegriffen wird. Falls sie jedoch indexiert ist, können Sie den read API, um auf den Wert aus dem Index zuzugreifen.

Leistung

Eine von STORED generierte Spalte hat keinen Einfluss auf die Leistung eines Lese- oder Abfragevorgangs. Nicht gespeicherte generierte Spalten, die in einer Abfrage verwendet werden, können jedoch die Leistung aufgrund des Aufwands bei der Auswertung des generierten Spaltenausdrucks.

Beeinträchtigt die Leistung von Schreibvorgängen (DML-Anweisungen und -Mutationen) wenn Sie entweder eine mit STORED generierte Spalte oder eine generierte Spalte verwenden, die indexiert. Der Aufwand ist auf die Auswertung des generierten Spaltenausdrucks zurückzuführen, fügt der Schreibvorgang eine der Spalten, auf die im generierten Spaltenausdruck. Da der Aufwand je nach Schreibvorgang für die Anwendung, das Schemadesign und die Dataset-Eigenschaften. Sie Ihre Anwendungen Benchmarking, bevor Sie eine generierte Spalte verwenden.

Nächste Schritte

• Weitere Informationen zum Informationsschema für GoogleSQL-Datenbanken und zum Informationsschema für PostgreSQL-Datenbanken

• Weitere Informationen zu generierten Spalten finden Sie in den Parameterdetails zu TABELLE ERSTELLEN.