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 CREATE TABLE
suivant, nous créons une table pour stocker des informations sur les utilisateurs. Nous disposons de 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
.
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)
);
La valeur de FullName
est calculée lorsqu'une nouvelle ligne est insérée ou lorsque FirstName
et/ou LastName
sont mis à jour pour une ligne existante. La valeur calculée est stockée avec les autres colonnes de la table. Le code SQL entre parenthèses est appelé expression de génération.
expression
peut être toute expression SQL valide pouvant être attribuée au type de données de 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.
L'expression ne peut pas contenir de fonctions non déterministes telles que
PENDING_COMMIT_TIMESTAMP()
,CURRENT_DATE()
etCURRENT_TIMESTAMP()
.Vous ne pouvez pas modifier l'expression d'une colonne générée.
L'attribut
STORED
qui suit l'expression stocke le résultat de l'expression avec les autres colonnes de la table. Les mises à jour ultérieures de l'une des colonnes référencées entraînent la réévaluation et le stockage de l'expression par Spanner.Spanner n'autorise pas les colonnes générées, sauf si elles utilisent l'attribut
STORED
.Les écritures directes sur les colonnes générées ne sont pas autorisées.
L'option "
allow_commit_timestamp
" n'est pas autorisée sur les colonnes générées ni sur celles qui référencent des colonnes.Vous ne pouvez pas modifier le type de données d'une colonne générée ni d'une colonne référencée par la colonne générée.
Vous ne pouvez pas supprimer une colonne à laquelle une colonne générée fait référence.
Vous pouvez utiliser une colonne générée comme clé primaire, avec les restrictions supplémentaires suivantes:
La clé primaire générée ne peut pas référencer d'autres colonnes générées.
La clé primaire générée peut faire référence à une colonne autre que celle de clé.
La clé primaire générée ne peut pas dépendre d'une colonne non-clé avec une clause
DEFAULT
.
Les règles suivantes s'appliquent lorsque vous utilisez des colonnes de clé générées:
- API de lecture: vous devez spécifier complètement les colonnes de clé, y compris les colonnes de clé générées.
- API de mutation: pour
INSERT
,INSERT_OR_UPDATE
etREPLACE
, Spanner ne vous permet pas de spécifier des colonnes de clé générées. PourUPDATE
, vous pouvez éventuellement spécifier des colonnes de clé générées. PourDELETE
, vous devez spécifier entièrement les colonnes de clé, y compris les clés générées. - LMD: vous ne pouvez pas écrire explicitement dans des clés générées dans les instructions
INSERT
ouUPDATE
. - Requête: en général, nous vous recommandons d'utiliser la colonne de clé générée comme filtre dans votre requête. Si l'expression de la colonne de clé générée n'utilise qu'une seule colonne comme référence, la requête peut appliquer une condition d'égalité (
=
) ouIN
à la colonne référencée. Pour en savoir plus et obtenir un exemple, consultez la section Créer 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;
Cela équivaut à l'instruction suivante, qui n'utilise pas la colonne générée.
GoogleSQL
SELECT Id, ARRAY_TO_STRING([FirstName, LastName], " ") as FullName
FROM Users;
PostgreSQL
SELECT id, firstname || ' ' || lastname as fullname
FROM users;
Outre la simplification de la requête et l'économie du coût d'évaluation de l'expression au moment de la requête, une colonne générée peut également être indexée ou utilisée en tant que clé étrangère.
Créer un index sur une colonne générée
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 ajoutons 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;
L'ajout d'une colonne générée stockée à une table existante est une opération de longue durée qui permet de remplir les valeurs de la colonne. Lors du remplissage, les colonnes générées stockées ne peuvent pas être lues ni interrogées. L'état de remplissage est reflété dans 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 et qui renvoie
NULL
dans le cas contraire.GoogleSQL
ALTER TABLE Users ADD COLUMN AgeAbove18 INT64 AS (IF(Age > 18, Age, NULL)) STORED;
PostgreSQL
ALTER TABLE Users ADD COLUMN AgeAbove18 BIGINT GENERATED ALWAYS AS (nullif( Age , least( 18, Age) )) STORED;
Créez un index sur cette nouvelle colonne et désactivez l'indexation des valeurs
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 index normal, car il exclut toutes les personnes de 18 ans et moins.GoogleSQL
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 sur un âge différent, par exemple pour récupérer tous les utilisateurs de plus de 21 ans, utilisez le même index et le filtre de 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;
Supprimer une colonne générée
L'instruction LDD suivante supprime une colonne générée à partir de la table Users
.
ALTER TABLE Users
DROP COLUMN Initials;
Modifier une expression de colonne générée
Vous ne pouvez pas modifier l'expression d'une colonne générée. À la place, vous devez supprimer la colonne existante et créer une colonne générée avec la nouvelle expression.
Créer une clé primaire sur une colonne générée
Dans Spanner, vous pouvez utiliser une colonne générée dans la clé primaire.
L'exemple suivant montre une instruction LDD qui crée la table UserInfoLog
avec une colonne générée ShardId
. La valeur de la colonne ShardId
dépend d'une autre colonne. Elle est dérivée à l'aide d'une fonction MOD
sur la colonne 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 colonnes clés. Dans l'exemple précédent, cela signifie fournir à la fois un ShardId
et un UserId
. Cependant, Spanner peut parfois déduire la valeur de la colonne de clé primaire générée s'il dépend d'une autre colonne et si la valeur de la colonne dont elle dépend est entièrement déterminée. Cela s'applique si la colonne référencée par la colonne de clé primaire générée remplit l'une des conditions suivantes:
- Elle est égale à une valeur constante ou à un paramètre lié dans la clause
WHERE
, ou - Sa valeur est définie par un opérateur
IN
dans la clauseWHERE
. - Elle tire sa valeur d'une condition d'équi jointure
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 du UserId
fourni.
La requête précédente est équivalente à la requête suivante après l'optimisation des requêtes:
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 expression 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 INFORMATION_SCHEMA
de Spanner contient des informations sur les colonnes générées sur votre base de données. Voici quelques exemples des questions auxquelles vous pouvez 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 ?
SELECT c.TABLE_NAME, c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS as c
WHERE c.GENERATION_EXPRESSION IS NOT NULL;
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 transmettre 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 connaître 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 n'a aucune incidence sur les performances d'une opération de lecture ou de requête. Toutefois, cela peut affecter les performances des opérations d'écriture ("instructions LMD" et "Mutations") en raison de la surcharge d'évaluation de l'expression de colonne d'une colonne générée si une opération d'écriture modifie l'une des colonnes référencées dans l'expression de colonne générée. Étant donné que les frais généraux varient en fonction de la charge de travail d'écriture pour l'application, de la conception du schéma et des caractéristiques de l'ensemble de données, nous vous recommandons de comparer vos applications avant d'utiliser une colonne générée.
Étapes suivantes
En savoir plus sur le SCHEMA D'INFORMATIONS de Spanner
Pour en savoir plus sur les colonnes générées, consultez la section CRÉER UNE TABLE.