Leçon 9.4 · Temps de lecture : ~7 min

Une table temporaire SQL est une table qui n'existe que pour la session (connexion) en cours et qui est supprimée automatiquement à sa fin. Dans cette leçon, vous apprendrez à créer des tables temporaires, à les remplir à partir d'une requête, en quoi elles diffèrent des tables ordinaires et des CTE, et quelles limitations elles ont dans MySQL.

Tables temporaires SQL

Une analyse complexe se fait souvent par étapes : sélectionner les clients qui nous intéressent, puis calculer plusieurs rapports à leur sujet. Le résultat intermédiaire peut être répété comme sous-requête ou CTE dans chaque rapport, ou enregistré une fois dans une table temporaire et réutilisé par toutes les requêtes suivantes.

Les bases d'exemple de SQLtest sont en lecture seule, CREATE TEMPORARY TABLE y renvoie donc une erreur de permission. Les exemples utilisent le schéma Sakila.

Qu'est-ce qu'une table temporaire

Une table temporaire ressemble à une table ordinaire et fonctionne comme elle : on peut insérer, sélectionner, modifier et supprimer des lignes. Les différences tiennent à sa durée de vie et à sa visibilité :

• elle n'est visible que dans la session qui l'a créée ; les autres connexions ne la voient pas, même si elles créent une table temporaire du même nom ;
• elle est supprimée automatiquement à la fin de la session ;
• elle est destinée aux données intermédiaires, pas au stockage permanent.

Syntaxe

CREATE TEMPORARY TABLE table_name (
    column1 data_type,
    column2 data_type
);

Le plus souvent, une table temporaire est créée directement à partir d'une requête :

CREATE TEMPORARY TABLE table_name AS
SELECT ...;

Exemple : réutiliser un résultat intermédiaire

Enregistrons les clients qui ont effectué plus de 30 paiements :

CREATE TEMPORARY TABLE tmp_active_customers AS
SELECT customer_id, COUNT(*) AS payment_count
FROM payment
GROUP BY customer_id
HAVING COUNT(*) > 30;

Plusieurs requêtes indépendantes peuvent maintenant l'utiliser sans répéter l'agrégation :

SELECT ac.customer_id, c.first_name, c.last_name, ac.payment_count
FROM tmp_active_customers AS ac
INNER JOIN customer AS c ON c.customer_id = ac.customer_id
ORDER BY ac.payment_count DESC;

SELECT COUNT(*) AS customers,
       AVG(payment_count) AS avg_payments,
       MAX(payment_count) AS max_payments
FROM tmp_active_customers;

Résultat : la liste des clients les plus actifs et des chiffres de synthèse pour le même groupe, calculé une seule fois.

Quand vous n'avez plus besoin de la table dans une longue session, supprimez-la explicitement :

DROP TEMPORARY TABLE IF EXISTS tmp_active_customers;

Dans MySQL, DROP TEMPORARY TABLE ne supprime qu'une table temporaire : il ne peut donc pas supprimer par erreur une table ordinaire du même nom.

Table temporaire vs table ordinaire

La dernière ligne est un piège : si une table temporaire porte le nom d'une table existante, elle masque cette table pour le reste de la session. Utilisez un préfixe comme tmp_.

Table temporaire vs CTE

Utilisez une CTE (leçon 6.4) quand le résultat intermédiaire sert dans une seule requête. Utilisez une table temporaire quand il sert dans plusieurs requêtes, quand il est coûteux à calculer, ou quand il est volumineux et profite d'un index :

CREATE TEMPORARY TABLE tmp_action_customers AS
SELECT DISTINCT r.customer_id
FROM rental AS r
INNER JOIN inventory AS i ON i.inventory_id = r.inventory_id
INNER JOIN film_category AS fc ON fc.film_id = i.film_id
INNER JOIN category AS c ON c.category_id = fc.category_id
WHERE c.name = 'Action';

ALTER TABLE tmp_action_customers ADD PRIMARY KEY (customer_id);

Limitations dans MySQL

• Une seule référence par requête. MySQL n'autorise pas une table temporaire à apparaître deux fois dans la même requête, par exemple dans une auto-jointure ou à la fois dans la requête externe et une sous-requête : l'erreur est « Can't reopen table ». Utilisez alors une CTE ou une table ordinaire.
• Réplication et pools de connexions. Dans les applications qui réutilisent les connexions, une table temporaire peut survivre jusqu'à la requête suivante sur la même connexion. Supprimez-la explicitement quand vous avez terminé.
• Moteur de stockage. Les tables temporaires sont stockées en mémoire ou sur disque selon leur taille. ENGINE = MEMORY garde une table en RAM, mais ne prend pas en charge les colonnes TEXT/BLOB et est limité par max_heap_table_size.

Points clés :

• Une table temporaire vit jusqu'à la fin de la session et n'est visible que dans cette session.
• CREATE TEMPORARY TABLE ... AS SELECT enregistre le résultat d'une requête.
• Une table temporaire peut être réutilisée par de nombreuses requêtes et indexée ; une CTE n'existe que dans une instruction.
• Utilisez un préfixe tmp_ : une table temporaire masque une table ordinaire du même nom.
• Dans MySQL, une table temporaire ne peut être référencée qu'une fois par requête.

Questions fréquentes

Quand une table temporaire est-elle supprimée ?

Quand la session qui l'a créée se termine. Vous pouvez la supprimer plus tôt avec DROP TEMPORARY TABLE table_name dans MySQL ou DROP TABLE table_name dans les autres bases.

Quelle est la différence entre une table temporaire et une CTE ?

Une CTE n'existe qu'à l'intérieur d'une instruction SQL. Une table temporaire est une vraie table qui existe jusqu'à la fin de la session : plusieurs requêtes distinctes peuvent l'utiliser, et elle peut avoir des index.

Les autres utilisateurs voient-ils ma table temporaire ?

Non. Une table temporaire n'est visible que dans la session qui l'a créée. Deux sessions peuvent même avoir des tables temporaires du même nom sans aucun conflit.