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 des informations sur les utilisateurs. Nous disposons de colonnes pour FirstName
et LastName
, et nous 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 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.
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 entraîne le stockage de la fonction 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.Les colonnes générées sans l'attribut
STORED
ne sont pas autorisées.Les écritures directes sur les colonnes générées ne sont pas autorisées.
Vous ne pouvez pas utiliser les colonnes générées en tant que clé primaire ou en tant que partie d'une clé primaire. Elles peuvent toutefois être des clés d'index secondaires.
L'option
allow_commit_timestamp
n'est pas autorisée sur les colonnes générées ni sur les colonnes référencées par des colonnes générées.Vous ne pouvez pas modifier le type de données d'une colonne générée, ni les colonnes référencées 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.
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
ci-dessous, 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 à 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 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 Google SQL ou le prédicatIS NOT NULL
dans PostgreSQL. Cet index partiel est plus petit et plus efficace qu'un index normal, car il exclut tous les utilisateurs âgés de 18 ans ou 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 autre âge, par exemple, pour extraire tous les utilisateurs de plus de 21 ans, utilisez le même index et le même filtre sur 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. Vous devez donc supprimer la colonne existante et créer une colonne générée avec la nouvelle expression.
Afficher les propriétés d'une colonne générée
INFORMATION_SCHEMA de Spanner contient des informations sur les colonnes générées de votre base de données. Voici quelques exemples de questions auxquelles vous pouvez répondre en interrogeant le schéma d'information.
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 afficher son état actuel pour déterminer, par exemple, si la colonne a été remplie et est entièrement utilisable. Si SPANNER_STATE
renvoie WRITE_ONLY
dans la requête suivante, cela signifie que Spanner est toujours en train de remplir la colonne et qu'aucune lecture n'est autorisée.
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;
Étapes suivantes
Apprenez-en plus sur le SCHEMA D'INFORMATIONS de Spanner.
Pour en savoir plus sur les colonnes générées, consultez les détails du paramètre CREATE TABLE.