Créer et gérer des colonnes générées

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 allons créer une table pour stocker des 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.

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

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.

  • Cette expression peut être toute expression SQL valide pouvant être attribuée au type de données de la colonne avec les restrictions suivantes.

  • 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 de colonne 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.

SELECT Id, FullName
FROM Users;

Cela équivaut à l'instruction suivante, qui n'utilise pas la colonne générée.

SELECT Id, ARRAY_TO_STRING([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.

CREATE INDEX UsersByFullName 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.

ALTER TABLE Users ADD COLUMN Initials STRING(2)
AS (ARRAY_TO_STRING([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 du 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.

  1. 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.

    ALTER TABLE Users ADD COLUMN AgeAbove18 INT64
    AS (IF(Age > 18, Age, NULL)) STORED;
    
  2. Créez un index sur cette nouvelle colonne, puis désactivez l'indexation des valeurs NULL avec le mot clé NULL_FILTERED. 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.

    CREATE NULL_FILTERED INDEX UsersAbove18ByAge
    ON Users (AgeAbove18);
    
  3. Pour récupérer les propriétés Id et Age de tous les utilisateurs âgés de plus de 18 ans, exécutez la requête suivante.

    SELECT Id, Age
        FROM Users@{FORCE_INDEX=UsersAbove18ByAge}
        WHERE AgeAbove18 IS NOT NULL;
    
  4. 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 :

    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

L'élément INFORMATION_SCHEMA de Cloud 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 Cloud Spanner est en cours de remplissage de la colonne et qu'aucune lecture n'est autorisée.

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;

Étape suivante