Tables temporaires

• Vues
• Sommaire
• Vues matérialisées

Ce chapitre est consacré aux tables temporaires qui, comme leur nom l'indique, sont des tables ayant une durée de vie très limitée. En effet, tout comme les variables utilisateur ou les requêtes préparées, les tables temporaires n'existent que dans la session qui les a créées. Dès que la session se termine (déconnexion volontaire ou accidentelle), les tables temporaires sont supprimées.

Nous verrons en détail dans ce chapitre comment se comportent ces tables temporaires, et à quoi elles peuvent servir. De plus, nous verrons deux nouvelles manières de créer une table (temporaire ou non), en partant de la structure et/ou des données d'une ou plusieurs autres tables.

• Principe, règles et comportement
• Méthodes alternatives de création des tables
• Utilité des tables temporaires

Principe, règles et comportement

On l'a dit, une table temporaire est une table qui n'existe que dans la session qui l'a créée. En dehors de ça, c'est une table presque normale. On peut exécuter sur ces tables toutes les opérations que l'on exécute sur une table classique : insérer des données, les modifier, les supprimer, et bien sûr les sélectionner.

Création, modification, suppression d'une table temporaire

Pour créer, modifier et supprimer une table temporaire, on utilise les mêmes commandes que pour les tables normales.

Création

Pour créer une table temporaire, on peut utiliser tout simplement CREATE TABLE en ajoutant TEMPORARY, pour préciser qu'il s'agit d'une table temporaire.

Exemple : création d'une table temporaire TMP_Animal.

1
2
3
4
5
6
7
8

CREATE TEMPORARY TABLE TMP_Animal (
    id INT UNSIGNED PRIMARY KEY,
    nom VARCHAR(30),
    espece_id INT UNSIGNED,
    sexe CHAR(1)
);

DESCRIBE TMP_Animal;

Visiblement, la table a bien été créée. Par contre, si l'on tente de vérifier cela en utilisant SHOW TABLES plutôt que DESCRIBE TMP_Animal, on ne trouvera pas la nouvelle table temporaire dans la liste des tables. Et pour cause, seules les tables permanentes (et les vues) sont listées par cette commande.

Modification

Pour la modification, nul besoin du mot-clé TEMPORARY, on utilise directement ALTER TABLE, comme s'il s'agissait d'une table normale.

Exemple : ajout d'une colonne à TMP_Animal.

1
2

ALTER TABLE TMP_Animal
ADD COLUMN date_naissance DATETIME;

Suppression

En ce qui concerne la suppression, on a le choix d'ajouter TEMPORARY, ou non. Si TEMPORARY est précisé, la table mentionnée ne sera supprimée que s'il s'agit bien d'une table temporaire. Sans ce mot-clé, on pourrait supprimer par erreur une table non temporaire, en cas de confusion des noms des tables par exemple.

Exemple : suppression de TMP_Animal, qui n'aura pas fait long feu.

1

DROP TEMPORARY TABLE TMP_Animal;

Utilisation des tables temporaires

Une table temporaire s'utilise comme une table normale. Les commandes d'insertion, modification et suppression de données sont exactement les mêmes.

Notez que l'immense majorité des tables temporaires étant créées pour stocker des données venant d'autres tables, on utilise souvent INSERT INTO ... SELECT pour l'insertion des données.

Cache-cache table

Une table temporaire existe donc uniquement pour la session qui la crée. Par conséquent, deux sessions différentes peuvent parfaitement utiliser chacune une table temporaire ayant le même nom, mais ni la même structure ni les mêmes données.

Il est également possible de créer une table temporaire ayant le même nom qu'une table normale. Toutes les autres sessions éventuelles continueront à travailler sur la table normale comme si de rien n'était.

En cas de conflit de noms entre une table temporaire et une table permanente, la table temporaire va masquer la table permanente. Donc, tant que cette table temporaire existera (c'est-à-dire jusqu'à ce qu'on la supprime explicitement, ou jusqu'à déconnexion de la session), toutes les requêtes faites en utilisant son nom seront exécutées sur la table temporaire, et non sur la table permanente de même nom.

Exemple : création d'une table temporaire nommée Animal.

On commence par sélectionner les perroquets de la table Animal (la table normale, puisqu'on n'a pas encore créé la table temporaire). On obtient quatre résultats.

1
2
3

SELECT id, sexe, nom, commentaires, espece_id 
FROM Animal
WHERE espece_id = 4;

On crée ensuite la table temporaire, et on y insère un animal avec espece_id = 4. On refait la même requête de sélection, et on obtient uniquement l'animal que l'on vient d'insérer, avec la structure de la table temporaire.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

CREATE TEMPORARY TABLE Animal (
    id INT UNSIGNED PRIMARY KEY,
    nom VARCHAR(30),
    espece_id INT UNSIGNED,
    sexe CHAR(1)
);

INSERT INTO Animal
VALUES (1, 'Tempo', 4, 'M');

SELECT * 
FROM Animal
WHERE espece_id = 4;

Pour terminer, suppression de la table temporaire. La même requête de sélection nous affiche à nouveau les quatre perroquets de départ.

1
2
3
4
5

DROP TEMPORARY TABLE Animal;

SELECT id, sexe, nom, commentaires, espece_id 
FROM Animal
WHERE espece_id = 4;

Dans ce cas de figure, il vaut mieux utiliser le mot-clé TEMPORARY dans la requête de suppression de la table, afin d'être sûr qu'on détruit bien la table temporaire et non la table permanente.

Restrictions des tables temporaires

Deux restrictions importantes à noter lorsque l'on travaille avec des tables temporaires :

• on ne peut pas mettre de clé étrangère sur une table temporaire ;
• on ne peut pas faire référence à une table temporaire deux fois dans la même requête.

Quelques explications sur la deuxième restriction : lorsque l'on parle de "faire référence deux fois à la table", il s'agit d'aller chercher des données deux fois dans la table. Le nom de la table temporaire peut apparaître plusieurs fois dans la requête, par exemple pour préfixer le nom des colonnes.

Exemples : on recrée une table TMP_Animal, un peu plus complexe cette fois, qu'on remplit avec les chats de la table Animal.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

CREATE TEMPORARY TABLE TMP_Animal (
    id INT UNSIGNED PRIMARY KEY,
    nom VARCHAR(30),
    espece_id INT UNSIGNED,
    sexe CHAR(1),
    mere_id INT UNSIGNED,
    pere_id INT UNSIGNED
);

INSERT INTO TMP_Animal
SELECT id, nom, espece_id, sexe, mere_id, pere_id 
FROM Animal
WHERE espece_id = 2;

1. Référence n'est pas occurrence

1
2
3

SELECT TMP_Animal.nom, TMP_Animal.sexe
FROM TMP_Animal
WHERE nom LIKE 'B%';

Ici, TMP_Animal apparaît trois fois dans la requête. Cela ne pose aucun problème, puisque la table n'est utilisée qu'une fois pour en extraire des données : dans le FROM.

2. Auto-jointure

On essaye d'afficher le nom des chats, ainsi que le nom de leur père quand on le connaît.

1
2
3
4

SELECT TMP_Animal.nom, TMP_Pere.nom AS pere 
FROM TMP_Animal
INNER JOIN TMP_Animal AS TMP_Pere 
    ON TMP_Animal.pere_id = TMP_Pere.id;

1

ERROR 1137 (HY000): Can't reopen table: 'TMP_Animal'

Cette fois, cela ne fonctionne pas : on extrait en effet des données de TMP_Animal, et de TMP_Animal AS TMP_Pere.

3. Sous-requête

On affiche le nom des animaux référencés comme pères.

1
2
3

SELECT nom
FROM TMP_Animal
WHERE id IN (SELECT pere_id FROM TMP_Animal);

1

ERROR 1137 (HY000): Can't reopen table: 'TMP_Animal'

À nouveau, on extrait des données de TMP_Animal à deux endroits différents : dans la requête et dans la sous-requête. Cela ne peut pas fonctionner.

Interaction avec les transactions

Un comportement assez intéressant lié aux tables temporaires concerne les transactions. On a vu que les commandes de création d'objets en tout genre provoquaient la validation implicite de la transaction dans laquelle la commande était faite. Ainsi, CREATE TABLE provoque une validation implicite.

Mais ce n'est pas le cas pour les tables temporaires : CREATE TEMPORARY TABLE et DROP TEMPORARY TABLE ne valident pas les transactions.

Cependant ces deux commandes ne peuvent pas être annulées par un ROLLBACK.

Exemple

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

START TRANSACTION;

INSERT INTO Espece (nom_courant, nom_latin)
VALUES ('Gerbille de Mongolie', 'Meriones unguiculatus');

CREATE TEMPORARY TABLE TMP_Test (id INT);

ROLLBACK;

SELECT id, nom_courant, nom_latin, prix FROM Espece;

SELECT * FROM TMP_Test;

1

Empty set (0.00 sec)

Il n'y a pas de gerbille dans nos espèces, puisque l'insertion a été annulée. Par contre, la création de la table TMP_Test, bien que faite à l'intérieur d'une transaction annulée, a bel et bien été exécutée. On n'obtient en effet aucune erreur lorsque l'on fait une sélection sur cette table (mais bien un résultat vide, puisqu'il n'y a aucune donnée dans cette table).

Méthodes alternatives de création des tables

Les deux méthodes de création de tables présentées ici sont valables pour créer des tables normales ou des tables temporaires. Elles se basent toutes les deux sur des tables et/ou des données préexistantes. Le choix de présenter ces méthodes dans le cadre des tables temporaires s'explique par le fait qu'en général, celles-ci sont créées pour stocker des données provenant d'autres tables, et par conséquent, sont souvent créées en utilisant une de ces deux méthodes.

Créer une table à partir de la structure d'une autre

Il est possible de créer la copie exacte d'une table, en utilisant la commande suivante :

1
2

CREATE [TEMPORARY] TABLE nouvelle_table
LIKE ancienne_table;

Les types de données, les index, les contraintes, les valeurs par défaut, le moteur de stockage, toutes les caractéristiques de ancienne_table seront reproduites dans nouvelle_table. Par contre, les données ne seront pas copiées : nouvelle_table sera vide.

Exemple : reproduction de la table Espece.

1
2
3
4
5
6

CREATE TABLE Espece_copy
LIKE Espece;

DESCRIBE Espece;

DESCRIBE Espece_copy;

Les deux commandes DESCRIBE renvoient exactement la même chose :

Ensuite, pour remplir la nouvelle table avec tout ou partie des données de la table d'origine, il suffit d'utiliser la commande INSERT INTO ... SELECT.

Exemple

1
2
3
4
5
6

INSERT INTO Espece_copy
SELECT * FROM Espece
WHERE prix < 100;

SELECT id, nom_courant, prix 
FROM Espece_copy;

Tables temporaires

Si l'on crée une table temporaire avec cette commande, tous les attributs de la table d'origine seront conservés, sauf les clés étrangères, puisqu'on ne peut avoir de clé étrangère dans une table temporaire.

Exemple : copie de la table Animal

1
2
3
4
5
6
7
8

CREATE TEMPORARY TABLE Animal_copy
LIKE Animal;

INSERT INTO Animal (nom, sexe, date_naissance, espece_id)
VALUES ('Mutant', 'M', NOW(), 12);

INSERT INTO Animal_copy (nom, sexe, date_naissance, espece_id)
VALUES ('Mutant', 'M', NOW(), 12);

Aucune espèce n'a 12 pour id, l'insertion dans Animal échoue donc à cause de la clé étrangère. Par contre, dans la table temporaire Animal_copy, l'insertion réussit.

Créer une table à partir de données sélectionnées

Cette seconde méthode ne se base pas sur la structure d'une table, mais sur les données récupérées par une requête SELECT pour construire une table, et y insérer des données. On fait donc ici d'une pierre deux coups : création de la table et insertion de données dans celle-ci.

1
2

CREATE [TEMPORARY] TABLE nouvelle_table
SELECT ...

Le type des colonnes de la nouvelle table sera déduit des colonnes sélectionnées. Par contre, la plupart des caractéristiques des colonnes sélectionnées seront perdues :

• les index ;
• les clés (primaires ou étrangères) ;
• l'auto-incrémentation.

Les valeurs par défaut et les contraintes NOT NULL seront par contre conservées.

Exemple : suppression puis recréation d'une table temporaire Animal_copy contenant tous les rats de la table Animal.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

DROP TABLE Animal_copy;

CREATE TEMPORARY TABLE Animal_copy
SELECT *
FROM Animal
WHERE espece_id = 5;

DESCRIBE Animal;

DESCRIBE Animal_copy;

Les types de colonnes et les contraintes NOT NULL sont les mêmes, mais les index ont disparu, ainsi que l'auto-incrémentation.

Forcer le type des colonnes

On peut laisser MySQL déduire le type des colonnes du SELECT, mais il est également possible de préciser le type que l'on désire, en faisant attention à la compatibilité entre les types que l'on précise et les colonnes sélectionnées. On peut également préciser les index désirés, les clés et l'éventuelle colonne à auto-incrémenter.

La syntaxe est alors similaire à un CREATE [TEMPORARY] TABLE classique, si ce n'est qu'on rajoute une requête de sélection à la suite.

Exemple : recréation d'Animal_copy, en modifiant quelques types et attributs.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

DROP TABLE Animal_copy;

CREATE TEMPORARY TABLE Animal_copy (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    sexe CHAR(1),
    date_naissance DATETIME,
    nom VARCHAR(100),
    commentaires TEXT,
    espece_id INT NOT NULL,
    race_id INT,
    mere_id INT,
    pere_id INT,
    disponible BOOLEAN DEFAULT TRUE,
    INDEX (nom(10))
) ENGINE=InnoDB
SELECT *
FROM Animal
WHERE espece_id = 5;

DESCRIBE Animal_copy;

Les colonnes id, espece_id, race_id, mere_id et pere_id ne sont plus des SMALLINT mais des INT, et seul id est UNSIGNED. Par contre, on a bien un index sur nom, et une clé primaire auto-incrémentée avec id. On n'a pas précisé de contrainte NOT NULL sur date_naissance, donc il n'y en a pas, bien que cette contrainte existe dans la table d'origine.

Nom des colonnes

Les noms des colonnes de la table créée correspondront aux noms des colonnes dans la requête SELECT. Il est bien sûr possible d'utiliser les alias dans celle-ci pour renommer une colonne. Cela implique que lorsque vous forcez le type des colonnes comme on vient de le voir, il est impératif que vos noms de colonnes correspondent à ceux de la requête SELECT, car l'insertion des données dans leurs colonnes respectives se fera sur la base du nom, et pas sur la base de la position.

Exemple : on recrée Animal_copy, mais les noms des colonnes sont dans le désordre, et certains ne correspondent pas à la requête SELECT.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

DROP TABLE Animal_copy;

CREATE TEMPORARY TABLE Animal_copy (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    nom VARCHAR(100),                            -- Ordre différent de la requête SELECT
    sexe CHAR(1),
    espece_id INT NOT NULL,                      -- Ordre différent de la requête SELECT
    date_naissance DATETIME,
    commentaires TEXT,
    race_id INT,
    maman_id INT,                                -- Nom de colonne différent de la requête SELECT
    papa_id INT,                                 -- Nom de colonne différent de la requête SELECT
    disponible BOOLEAN DEFAULT TRUE,
    INDEX (nom(10))
) ENGINE=InnoDB
SELECT id, sexe, date_naissance, nom, commentaires, espece_id, race_id, mere_id, pere_id, disponible
FROM Animal
WHERE espece_id = 5;

DESCRIBE Animal_copy;

Les colonnes mere_id et pere_id de la requête SELECT ne correspondaient à aucune colonne définie par la commande CREATE TABLE. Ces colonnes ont donc été créées dans la nouvelle table, mais leur type a été déduit à partir du SELECT. Quant aux colonnes maman_id et papa_id, elles ont bien été créées bien qu'elles ne correspondent à rien dans la sélection. Elles ne contiendront simplement aucune donnée.

Voyons maintenant le contenu d'Animal_copy.

1
2

SELECT maman_id, papa_id, id, sexe, nom, espece_id, mere_id, pere_id 
FROM Animal_copy;

Les données sont dans les bonnes colonnes, et les colonnes maman_id et papa_id sont vides, contrairement à mere_id et pere_id.

Une conséquence intéressante de ce comportement est qu'il est tout à fait possible de préciser le type et les attributs pour une partie des colonnes uniquement.

Exemple : Recréation d'Animal_copy. Cette fois on ne précise le type et les attributs que de la colonne id et on ajoute un index sur nom.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

DROP TABLE Animal_copy;

CREATE TEMPORARY TABLE Animal_copy (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    INDEX (nom(10))
) ENGINE=InnoDB
SELECT *
FROM Animal
WHERE espece_id = 5;

DESCRIBE Animal_copy;

Réunir les données de plusieurs tables

Puisque cette méthode de création de table se base sur une requête de sélection, cela signifie qu'on peut très bien créer une table sur la base d'une partie des colonnes d'une table existante. Cela signifie également que l'on peut créer une table à partir de données provenant de plusieurs tables différentes. Bien entendu, une table ne pouvant avoir deux colonnes de même nom, il ne faut pas oublier d'utiliser les alias pour renommer certaines colonnes en cas de noms dupliqués.

Exemple 1 : création de Client_mini, qui correspond à une partie de la table Client.

1
2
3

CREATE TABLE Client_mini
SELECT nom, prenom, date_naissance
FROM Client;

Exemple 2 : création de Race_espece, à partir des tables Espece et Race.

1
2
3
4

CREATE TABLE Race_espece
SELECT Race.id, Race.nom, Espece.nom_courant AS espece, Espece.id AS espece_id
FROM Race
INNER JOIN Espece ON Espece.id = Race.espece_id;

Utilité des tables temporaires

Les tables temporaires n'existant que le temps d'une session, leur usage est limité à des situations précises.

Gain de performance

Si, dans une même session, vous devez effectuer des calculs et/ou des requêtes plusieurs fois sur le même set de données, il peut être intéressant de stocker ce set de données dans une table temporaire, pour travailler sur cette table. En effet, une fois vos données de travail isolées dans une table temporaire, les requêtes vous servant à sélectionner les données qui vous intéressent seront simplifiées, donc plus rapides.

Typiquement, imaginons qu'on désire faire une série de statistiques sur les adoptions de chiens.

• Quelles races sont les plus demandées ?
• Y a-t-il une période de l'année pendant laquelle les gens adoptent beaucoup ?
• Nos clients ayant adopté un chien sont-ils souvent dans la même région ?
• Les chiens adoptés sont-ils plutôt des mâles ou des femelles ?
• …

On va avoir besoin de données provenant des tables Animal, Race, Client et Adoption, en faisant au minimum une jointure à chaque requête pour n'avoir que les données liées aux chiens. Il peut dès lors être intéressant de stocker toutes les données dont on aura besoin dans une table temporaire.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

CREATE TEMPORARY TABLE TMP_Adoption_chien
SELECT Animal.id AS animal_id, Animal.nom AS animal_nom, Animal.date_naissance AS animal_naissance, Animal.sexe AS animal_sexe, Animal.commentaires AS animal_commentaires,
Race.id AS race_id, Race.nom AS race_nom, 
Client.id AS client_id, Client.nom AS client_nom, Client.prenom AS client_prenom, Client.adresse AS client_adresse, 
Client.code_postal AS client_code_postal, Client.ville AS client_ville, Client.pays AS client_pays, Client.date_naissance AS client_naissance,
Adoption.date_reservation AS adoption_reservation, Adoption.date_adoption AS adoption_adoption, Adoption.prix
FROM Animal
LEFT JOIN Race ON Animal.race_id = Race.id
INNER JOIN Adoption ON Animal.id = Adoption.animal_id
INNER JOIN Client ON Client.id = Adoption.client_id
WHERE Animal.espece_id = 1;

Toutes les requêtes pour obtenir les statistiques et informations demandées pourront donc se faire directement sur TMP_Adoption_chien, ce qui sera plus rapide que de refaire chaque fois une requête avec jointure(s). Il peut bien sûr être intéressant d'ajouter des index sur les colonnes qui serviront souvent dans les requêtes.

Tests

Il arrive qu'on veuille tester l'effet des instructions, avant de les appliquer sur les données. Pour cela, on peut par exemple utiliser les transactions, et annuler ou valider les requêtes selon que l'effet produit correspond à ce qui était attendu ou non. Une autre solution est d'utiliser les tables temporaires. On crée des tables temporaires contenant les données sur lesquelles on veut faire des tests, on effectue les tests sur celles-ci, et une fois qu'on est satisfait du résultat, on applique les mêmes requêtes aux vraies tables. Cette solution permet de ne pas risquer de valider inopinément des requêtes de test. Sans oublier qu'il n'est pas toujours possible d'utiliser les transactions : si les tables sont de type MyISAM, les transactions ne sont pas disponibles. De même, les transactions sont inutilisables si les tests comprennent des requêtes provoquant une validation implicite des transactions.

Sets de résultats et procédures stockées

Lorsque l'on crée une procédure stockée, on peut utiliser les paramètres OUT et INOUT pour récupérer les résultats de la procédure. Mais ces paramètres ne peuvent contenir qu'un seul élément.

On peut avoir besoin de récupérer le résultat d'une requête SELECT, ou une liste de valeurs calculées dans la procédure. Ou encore, on peut vouloir passer un set de résultats d'une procédure stockée à une autre.

Une solution est d'utiliser une table temporaire pour stocker les résultats qu'on veut utiliser en dehors de la procédure.

Exemple : création par une procédure d'une table temporaire stockant les adoptions qui ne sont pas en ordre de paiement.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

DELIMITER |
CREATE PROCEDURE table_adoption_non_payee()
BEGIN
    DROP TEMPORARY TABLE IF EXISTS Adoption_non_payee;

    CREATE TEMPORARY TABLE Adoption_non_payee
    SELECT Client.id AS client_id, Client.nom AS client_nom, Client.prenom AS client_prenom, Client.email AS client_email,
           Animal.nom AS animal_nom, Espece.nom_courant AS espece, Race.nom AS race, 
           Adoption.date_reservation, Adoption.date_adoption, Adoption.prix
    FROM Adoption
    INNER JOIN Client ON Client.id = Adoption.client_id
    INNER JOIN Animal ON Animal.id = Adoption.animal_id
    INNER JOIN Espece ON Espece.id = Animal.espece_id
    LEFT JOIN Race ON Race.id = Animal.race_id
    WHERE Adoption.paye = FALSE; 
END |
DELIMITER ;

CALL table_adoption_non_payee();

SELECT client_id, client_nom, client_prenom, animal_nom, prix 
FROM Adoption_non_payee;

En résumé

• Une table temporaire est une table qui n'existe que pour la session dans laquelle elle a été créée. Dès que la session se termine, les tables temporaires sont supprimées.
• Une table temporaire est créée de la même manière qu'une table normale. Il suffit d'ajouter le mot-clé TEMPORARY avant TABLE.
• On peut créer une table (temporaire ou non) à partir de la structure d'une autre table avec CREATE [TEMPORARY] TABLE nouvelle_table LIKE ancienne_table;.
• On peut créer une table à partir d'une requête SELECT avec CREATE [TEMPORARY] TABLE SELECT ...;.
• Les tables temporaires permettent de gagner en performance lorsque, dans une session on doit exécuter plusieurs requêtes sur un même set de données.
• On peut utiliser les tables temporaires pour créer des données de test.
• Enfin, les tables temporaires peuvent être utilisées pour stocker un set de résultats d'une procédure stockée.

• Vues
• Sommaire
• Vues matérialisées