Un testeur souhaite effacer une table intermédiaire avant un nouveau test. Devrait-il être DELETE, TRUNCATE ou DROP ? Un mauvais choix peut signifier un travail lent, une table manquante ou, en production, un très mauvais après-midi.

Nous avons exécuté les exemples DELETE et DROP ci-dessous dans SQLite 3.50, et les résultats proviennent de cette exécution. SQLite n’a pas de commande TRUNCATE, donc les notes TRUNCATE sont basées sur la documentation officielle de PostgreSQL, MySQL et SQL Server plutôt que sur notre propre test.

La réponse courte

• Besoin de supprimer certaines lignes ? Utilisez DELETE ... WHERE.
• Besoin de vider une table mais de la conserver ? Utilisez TRUNCATE, là où il est pris en charge.
• Faut-il que la table disparaisse pour de bon ? Utilisez DROP TABLE.

SUPPRIMER : supprimer des lignes

DELETE est une commande de changement de données (DML). Il supprime les lignes une par une, déclenche des déclencheurs au niveau des lignes et peut être filtré. Nous mettons en place une petite table avec un déclencheur d’audit :

CREATE TABLE logs (id INTEGER PRIMARY KEY AUTOINCREMENT, level TEXT);
INSERT INTO logs (level) VALUES ('INFO'),('ERROR'),('INFO'),('DEBUG');

CREATE TABLE audit (msg TEXT);
CREATE TRIGGER logs_delete AFTER DELETE ON logs
BEGIN INSERT INTO audit VALUES ('deleted ' || OLD.id); END;

DELETE FROM logs WHERE level = 'DEBUG';

SELECT id, level FROM logs;  -- (1,'INFO'), (2,'ERROR'), (3,'INFO')
SELECT * FROM audit;         -- 'deleted 4'

Seule la ligne correspondante a disparu et le déclencheur l’a enregistrée. L’exécution de DELETE FROM logs sans clause WHERE a supprimé les trois autres lignes, et la table d’audit contenait alors quatre entrées, une par ligne supprimée.

DELETE ne réinitialise pas les compteurs à incrémentation automatique. Après avoir vidé la table, notre prochaine insertion a reçu id 5, pas 1. Dans SQLite, c’est à cause de AUTOINCREMENT. Les colonnes d’identité MySQL InnoDB et SQL Server continuent également là où elles se trouvaient après un DELETE.

TRUNCATE : vider la table

TRUNCATE TABLE logs; supprime chaque ligne à la fois. Il ne peut pas prendre de clause WHERE. Au lieu de supprimer les lignes une par une, la base de données libère généralement les pages de données de la table, ce qui explique pourquoi elle est beaucoup plus rapide sur les grandes tables.

Lorsque nous l’avons exécuté dans SQLite, nous avons eu une erreur de syntaxe, car SQLite n’a pas la commande :

TRUNCATE TABLE logs;
-- near "TRUNCATE": syntax error

Dans SQLite, utilisez DELETE FROM logs; sans clause WHERE. La documentation de SQLite décrit une “optimisation tronquée” qui accélère cette opération lorsque la table n’a pas de déclencheurs. Notre table avait un déclencheur, donc la suppression s’est exécutée ligne par ligne et l’a déclenchée pour chaque ligne.

Dans les bases de données prenant en charge TRUNCATE, par leur documentation :

• Il ne déclenche normalement pas les déclencheurs DELETE au niveau de la ligne.
• Il réinitialise généralement les compteurs d’identité ou d’auto-incrémentation, par exemple dans MySQL et SQL Server. Dans PostgreSQL, vous ajoutez RESTART IDENTITY.
• Il nécessite des autorisations plus fortes que DELETE dans la plupart des systèmes.

DROP : supprime la table elle-même

DROP TABLE est une commande de schéma (DDL). Il supprime les données, la définition de la table, ses index, ses déclencheurs et ses contraintes. Tout ce qui fait référence à la table, comme les vues ou les clés étrangères, est également affecté.

DROP TABLE logs;
SELECT * FROM logs;
-- no such table: logs

DROP TABLE IF EXISTS logs;  -- no error, even though it is gone

IF EXISTS est pratique dans les scripts d’installation qui peuvent s’exécuter plusieurs fois. Les vues sur une table supprimée peuvent être interrompues, comme nous l’avons montré dans Explication des vues SQL.

Tableau comparatif

Pouvez-vous revenir en arrière ?

C’est là que de nombreux articles simplifient à l’extrême. La réponse honnête est que cela dépend de la base de données.

DELETE peut être annulé dans une transaction partout. Nous l’avons testé :

BEGIN;
DELETE FROM logs;
SELECT COUNT(*) FROM logs;  -- 0
ROLLBACK;
SELECT COUNT(*) FROM logs;  -- 3

DROP a également été annulé dans SQLite, qui prend en charge le DDL transactionnel :

BEGIN;
DROP TABLE logs;
ROLLBACK;
SELECT COUNT(*) FROM logs;  -- 1 (the table is back)

Pour TRUNCATE et DROP dans d’autres bases de données, selon leur documentation :

Ainsi, “TRUNCATE ne peut jamais être annulé” est faux pour PostgreSQL et SQL Server, et “il peut toujours être annulé” est faux pour MySQL. En dehors d’une transaction, aucune d’entre elles ne peut être annulée sans sauvegarde.

Pourquoi TRUNCATE est rapide

DELETE enregistre chaque ligne supprimée afin que la modification puisse être annulée et répliquée, déclenche des déclencheurs et vérifie les contraintes ligne par ligne. Sur une table comportant des millions de lignes, cela crée beaucoup de travail et de volume de journaux. TRUNCATE libère généralement le stockage en une seule opération, en enregistrant beaucoup moins. Pour supprimer un grand sous-ensemble plutôt que tout, supprimez-le par lots afin d’éviter les longs verrous. Nous couvrons le coût du verrouillage et des requêtes dans Optimisation des requêtes SQL.

Clés étrangères et autorisations

• Clés étrangères. SQL Server refuse de tronquer une table référencée par une clé étrangère. PostgreSQL vous oblige également à tronquer les tables de référencement ou à utiliser CASCADE. Un DELETE vérifie à la place chaque ligne par rapport aux contraintes.
• Autorisations. DELETE nécessite des droits de suppression. TRUNCATE a souvent besoin d’ALTER ou d’un droit TRUNCATE dédié, et DROP a besoin de propriété ou d’ALTER sur le schéma.
• Réplication et audit. Si d’autres systèmes dépendent de journaux de modifications ou de déclencheurs au niveau des lignes, TRUNCATE peut les ignorer. Vérifiez d’abord auprès du propriétaire de ces systèmes.

Lequel utiliser

Pour les données de test, TRUNCATE est populaire car il réinitialise les identifiants, ce qui rend les tests reproductibles. Encapsuler chaque test dans une transaction annulée est une autre option, expliquée dans Transactions SQL et ACID.

Liste de contrôle de sécurité

1. Confirmez la base de données et l’environnement auxquels vous êtes connecté.
2. Exécutez d’abord la clause WHERE en tant que SELECT.
3. Enveloppez la modification dans une transaction où votre base de données la prend en charge.
4. Avoir une sauvegarde récente et testée avant TRUNCATE ou DROP en production.
5. Placez les instructions DROP dans des scripts de migration révisés, jamais dans des consoles ad hoc.
6. Vérifiez ce qui dépend d’une table, comme les vues, les clés étrangères et les rapports, avant de la supprimer.

Supprimer de grandes quantités de données en toute sécurité

Supprimer des millions de lignes avec un seul DELETE peut verrouiller la table pendant une longue période, remplir le journal des transactions et ralentir la réplication. Un modèle courant consiste à supprimer par petits lots et à répéter jusqu’à ce qu’il ne reste plus rien :

-- SQL Server style (not run for this article)
DELETE TOP (5000) FROM logs WHERE created_at < '2026-01-01';

-- PostgreSQL style (not run for this article)
DELETE FROM logs
WHERE id IN (SELECT id FROM logs WHERE created_at < '2026-01-01' LIMIT 5000);

Exécutez l’instruction en boucle, avec une courte pause entre les lots, jusqu’à ce qu’elle ne rapporte aucune ligne. Chaque lot est une petite transaction, les verrous sont donc courts et les autres requêtes continuent de s’exécuter. Un index sur la colonne de filtre, ici created_at, maintient chaque lot rapide.

Pour les données qui augmentent par date, telles que les journaux et les événements, le partitionnement des tables est encore meilleur. Chaque mois vit dans sa propre partition, et la suppression des anciennes données devient une opération rapide qui supprime ou tronque une partition au lieu de supprimer les lignes une par une.

Suppressions logicielles

De nombreuses applications ne suppriment jamais physiquement les enregistrements professionnels. Ils ajoutent une colonne telle que deleted_at et la définissent à la place, et les requêtes filtrent ensuite sur deleted_at IS NULL. Les suppressions logicielles facilitent la récupération et conservent l’historique des audits, mais chaque requête doit mémoriser le filtre, ce qui est un bon travail pour une vue. Les contraintes uniques doivent également être prises en compte, car une ligne supprimée de manière logicielle conserve toujours sa valeur. Les lois sur la confidentialité peuvent exiger la suppression réelle des données personnelles après un certain temps. Les suppressions logicielles ne remplacent donc pas une politique de conservation.

Pour conclure

DELETE est l’outil précis et prudent. TRUNCATE est le bouton de réinitialisation rapide et DROP supprime entièrement la table. Le plus gros risque est de supposer qu’une base de données se comporte comme une autre, notamment en cas de restauration. En cas de doute, testez d’abord la commande sur une copie de la table dans votre propre base de données.

Questions fréquentes

Continuez à apprendre