Une colonne générée est une colonne qui est toujours calculée à partir d'autres colonnes d'une ligne. Ces colonnes peuvent simplifier une requête, économiser le coût d'évaluation d'une expression au moment de la requête, et être indexées ou utilisées en tant que clé étrangère. Cet article explique comment gérer ce type de colonne dans votre base de données.
Ajouter une colonne générée à une nouvelle table
Dans l'extrait de code CREATE TABLE
suivant, nous créons une table pour stocker les informations
sur les utilisateurs. Nous avons des colonnes pour FirstName
et LastName
, et définissons une
colonne générée pour FullName
, qui est la concaténation de FirstName
et
LastName
Le code SQL entre parenthèses est appelé expression de génération.
Une colonne générée peut être marquée comme STORED
pour éviter le coût d'évaluation de la
au moment de la requête. Par conséquent, la valeur de FullName
n'est calculée
Lorsqu'une nouvelle ligne est insérée, ou lorsque FirstName
ou LastName
est mis à jour pour une
ligne existante. La valeur calculée est stockée avec d'autres colonnes dans le
tableau.
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)
);
Vous pouvez créer une colonne générée non stockée en omettant le STORED
dans le LDD. Ce type de colonne générée est évalué au moment de la requête
et peuvent simplifier
une requête. Dans PostgreSQL, vous pouvez créer
générée à l'aide de l'attribut VIRTUAL
.
GoogleSQL
FullName STRING(MAX) AS (CONCAT(FirstName, " ", LastName))
PostgreSQL
fullname text GENERATED ALWAYS AS (firstname || ' ' || lastname) VIRTUAL
expression
peut correspondre à n'importe quelle expression SQL valide attribuable au type de données de la colonne avec les restrictions suivantes.L'expression ne peut référencer que des colonnes dans la même table.
L'expression ne peut pas contenir de sous-requêtes.
Les expressions comportant des fonctions non déterministes telles que
PENDING_COMMIT_TIMESTAMP()
,CURRENT_DATE()
CURRENT_TIMESTAMP()
ne peuvent pas devenir une colonne générée au formatSTORED
ou une colonne générée est indexée.Vous ne pouvez pas modifier l'expression d'une colonne
STORED
ni d'une colonne générée indexée.
L'attribut
STORED
qui suit l'expression stocke le résultat de l'appel avec d'autres colonnes de la table. Suivant si les modifications apportées à l'une des colonnes référencées font référence réévaluer et stocker l'expression.Les colonnes générées qui ne sont pas
STORED
ne peuvent pas être marquées commeNOT NULL
.Les écritures directes sur les colonnes générées ne sont pas autorisées.
L'option de colonne
allow_commit_timestamp
n'est pas autorisée sur les colonnes générées ou toute colonne qui a généré des références à des colonnes.Vous ne pouvez pas modifier les données des colonnes
STORED
ou générées qui sont indexées type de la colonne ou de toute colonne référencée par la colonne générée.Vous ne pouvez pas supprimer une colonne référencée par une colonne générée.
Vous pouvez utiliser une colonne générée comme clé primaire avec les éléments suivants : restrictions supplémentaires:
La clé primaire générée ne peut pas faire référence à d'autres colonnes générées.
La clé primaire générée peut faire référence à une colonne non-clé au maximum.
La clé primaire générée ne peut pas dépendre d'une colonne non-clé avec une
DEFAULT
.
Les règles suivantes s'appliquent lorsque vous utilisez des colonnes de clé générées:
- API Read: vous devez spécifier complètement les colonnes de clé, y compris les des colonnes de clé générées.
- API de mutation: pour
INSERT
,INSERT_OR_UPDATE
etREPLACE
, Spanner ne vous permet pas de spécifier les colonnes de clé générées. PourUPDATE
, vous pouvez éventuellement spécifier les colonnes de clé générées. PourDELETE
, vous devez spécifier complètement les colonnes de clé, y compris les les clés générées. - LMD: vous ne pouvez pas écrire explicitement sur des clés générées dans
INSERT
ouUPDATE
. - Requête: en général, nous vous recommandons d'utiliser la colonne de clé générée comme
dans votre requête. Si l'expression de la clé générée
n'utilise qu'une seule colonne comme référence, la requête peut appliquer une valeur d'égalité
(
=
) ouIN
à la colonne de référence. Pour en savoir plus et Pour obtenir un exemple, consultez Créez une clé unique dérivée d'une colonne de valeur.
La colonne générée peut être interrogée comme n'importe quelle autre colonne, comme illustré dans l'exemple suivant.
GoogleSQL
SELECT Id, FullName
FROM Users;
PostgreSQL
SELECT id, fullname
FROM users;
La requête utilisant Fullname
est équivalente à la requête avec les identifiants générés
. Par conséquent, une colonne générée peut simplifier la requête.
GoogleSQL
SELECT Id, ARRAY_TO_STRING([FirstName, LastName], " ") as FullName
FROM Users;
PostgreSQL
SELECT id, firstname || ' ' || lastname as fullname
FROM users;
Créer un index sur une colonne générée
Vous pouvez également indexer ou utiliser une colonne générée en tant que clé étrangère.
Pour faciliter les recherches dans la colonne générée FullName
, nous pouvons créer un index secondaire, comme indiqué dans l'extrait suivant.
GoogleSQL
CREATE INDEX UsersByFullName ON Users (FullName);
PostgreSQL
CREATE INDEX UserByFullName ON users (fullname);
Ajouter une colonne générée à une table existante
À l'aide de l'instruction ALTER TABLE
suivante, nous pouvons ajouter une colonne générée à
la table Users
pour générer et stocker les initiales de l'utilisateur.
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;
Si vous ajoutez une colonne générée stockée à une table existante, une couche de données de longue durée pour remplir les valeurs de colonne est lancée. Lors du remplissage, les colonnes générées stockées ne peuvent pas être lues ou interrogées. L'état de remplissage est reflétée dans la table INFORMATION_SCHEMA.
Créer un index partiel à l'aide d'une colonne générée
Imaginons que nous souhaitions n'interroger que les utilisateurs de plus de 18 ans. Une analyse complète de la table serait inefficace, c'est pourquoi nous utilisons un index partiel.
Utilisez l'instruction suivante pour ajouter une autre colonne générée qui renvoie l'âge de l'utilisateur s'il a plus de 18 ans. Sinon, renvoie
NULL
.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;
Créez un index pour cette nouvelle colonne et désactivez l'indexation de
NULL
. avec le mot cléNULL_FILTERED
dans GoogleSQL ou le prédicatIS NOT NULL
dans PostgreSQL. Cet index partiel est plus petit et plus efficace qu'un indice normal, car il exclut toutes les personnes 18 ans ou moinsGoogleSQL
CREATE NULL_FILTERED INDEX UsersAbove18ByAge ON Users (AgeAbove18);
PostgreSQL
CREATE INDEX UsersAbove18ByAge ON users (AgeAbove18) WHERE AgeAbove18 IS NOT NULL;
Pour récupérer les propriétés
Id
etAge
de tous les utilisateurs âgés de plus de 18 ans, exécutez la requête suivante.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;
Pour filtrer les données en fonction d'une tranche d'âge différente, par exemple, pour récupérer tous les utilisateurs supérieur à 21, utilisez le même index et filtrez la colonne générée comme suit:
GoogleSQL
SELECT Id, Age FROM Users@{FORCE_INDEX=UsersAbove18ByAge} WHERE AgeAbove18 > 21;
PostgreSQL
SELECT Id, Age FROM users /*@ FORCE_INDEX = UsersAbove18ByAge */ WHERE AgeAbove18 > 21;
Une colonne indexée générée peut vous faire économiser le coût d'évaluation d'une au moment de la requête et évitez de stocker les valeurs deux fois (dans la table de base et l'index) par rapport à une colonne générée par
STORED
.
Supprimer une colonne générée
L'instruction LDD suivante supprime une colonne générée de la table Users
:
GoogleSQL
ALTER TABLE Users DROP COLUMN Initials;
PostgreSQL
ALTER TABLE users DROP COLUMN initials;
Modifier une expression de colonne générée
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;
Mettre à jour l'expression d'une colonne générée par STORED
ou d'une colonne indexée non stockée
générée n'est pas autorisée.
Créer une clé primaire sur une colonne générée
Dans Spanner, vous pouvez utiliser une colonne générée par STORED
dans la
clé primaire.
L'exemple suivant montre une instruction LDD qui crée l'objet UserInfoLog
table avec une colonne générée par ShardId
. Valeur de la colonne ShardId
dépend d'une autre colonne. Elle est obtenue à l'aide d'une fonction MOD
sur la
UserId
. ShardId
est déclaré dans la clé primaire.
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));
Normalement, pour accéder efficacement à une ligne spécifique, vous devez spécifier toutes les clés
colonnes. Dans l'exemple précédent, cela impliquerait de fournir à la fois un ShardId
et
UserId
Cependant, Spanner peut parfois déduire la valeur de la
colonne de clé primaire générée si elle dépend d'une seule autre colonne et si le
de la colonne dont elle dépend est entièrement déterminée. Cela est vrai si
référencée par celle de clé primaire générée correspond à l'un des critères
conditions:
- Elle est égale à une valeur constante ou à un paramètre de limite dans la clause
WHERE
. - Sa valeur est définie par un opérateur
IN
dans la clauseWHERE
. - Sa valeur provient d'une condition d'équijointure
Par exemple, pour la requête suivante :
GoogleSQL
SELECT * FROM UserInfoLog
AS T WHERE T.UserId=1;
PostgreSQL
SELECT * FROM UserInfoLog
AS T WHERE T.UserId=1;
Spanner peut déduire la valeur de ShardId
à partir de la UserId
fournie.
La requête précédente est équivalente à la requête suivante après requête
optimisation:
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;
L'exemple suivant montre comment créer la table Students
et utiliser une
qui récupère le champ id
de la colonne JSON StudentInfo
et
l'utilise comme clé primaire:
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));
Afficher les propriétés d'une colonne générée
Le champ INFORMATION_SCHEMA
de Spanner contient des informations sur les
colonnes de votre base de données. Voici quelques exemples de questions que vous
peut répondre lorsque vous interrogez le schéma d'informations.
Quelles colonnes générées sont définies dans ma base de données ?
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
est YES
pour les colonnes générées stockées, NO
pour les colonnes non stockées
colonnes générées ou NULL
pour les colonnes non générées.
Quelle est l'état actuel des colonnes générées dans le tableau Users
?
Si vous avez ajouté une colonne générée à une table existante, vous pouvez
transmettez SPANNER_STATE
dans une requête pour connaître l'état actuel de la colonne.
SPANNER_STATE
renvoie les valeurs suivantes:
COMMITTED
: la colonne est entièrement utilisable.WRITE_ONLY
: la colonne est en cours de remplissage. Aucune lecture n'est autorisée.
Utilisez la requête suivante pour déterminer l'état d'une colonne:
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;
Remarque: Une colonne générée non stockée ne peut être accessibles à l'aide de la requête SQL. Toutefois, s'il est indexé, vous pouvez utiliser le read pour accéder à la valeur de l'index.
Performance
Une colonne générée par STORED
n'a aucune incidence sur les performances d'une lecture ou d'une requête
opération. Toutefois, les colonnes générées non stockées utilisées dans une requête peuvent avoir un impact
en raison de la surcharge liée à l'évaluation de l'expression de colonne générée.
Les performances des opérations d'écriture (instructions LMD et mutations) sont affectées
lorsque vous utilisez une colonne générée par STORED
ou une colonne générée qui est
peut être indexée. La surcharge est due à l'évaluation de l'expression de colonne générée lorsque
l'opération d'écriture insère ou modifie l'une des colonnes référencées dans le
l'expression de colonne générée. Comme la surcharge varie en fonction de l'écriture
pour l'application, la conception du schéma et les caractéristiques de l'ensemble de données,
recommandent d'analyser vos applications avant d'utiliser une colonne générée.
Étape suivante
En savoir plus sur l'API Spanner Schéma d'informations pour les bases de données à dialecte GoogleSQL Schéma d'informations pour les bases de données de dialecte PostgreSQL.
Pour en savoir plus sur les colonnes générées, consultez les détails du paramètre CRÉER UNE TABLE.