Réponse

DELETE et TRUNCATE — les deux outils pour vider les tables, mais ils fonctionnent différemment :

DELETE supprime des lignes selon une condition ou toutes, prend en charge WHERE, peut être annulé, active les déclencheurs. Peut être lent avec un grand nombre de lignes (une par une).
TRUNCATE vide instantanément toute la table, ne peut pas utiliser WHERE, souvent non enregistré ligne par ligne, n'active pas toujours les déclencheurs. Dans la plupart des cas, l'action est irréversible et ne peut pas être annulée dans certaines SGBD.

Nuances :

DELETE prend en charge l'annulation de la transaction (ROLLBACK), TRUNCATE — non ou dépend de la SGBD.
TRUNCATE réinitialise généralement le compteur d'identité/séquence.
TRUNCATE n'est pas applicable s'il y a des clés externes ou des références à la table.

Exemple :

-- Supprimer les commandes de plus de 3 ans
DELETE FROM orders WHERE created_at < CURRENT_DATE - INTERVAL '3 years';

-- Vider complètement la table (rapidement)
TRUNCATE TABLE logs;

Question piège

Peut-on récupérer des données après un TRUNCATE ?

Erreur typique : on pense que comme pour DELETE, TRUNCATE peut être annulé par une transaction. Mais TRUNCATE ne peut généralement pas être annulé ou faire de l'UNDO, sauf dans certaines SGBD (comme PostgreSQL en travaillant à l'intérieur d'une transaction).

Exemple (dans la plupart des SGBD) :

BEGIN;
TRUNCATE TABLE orders;
ROLLBACK; -- Dans la plupart des SGBD, cela ne fonctionnera pas, les données sont perdues !

Exemples d'erreurs réelles dues à une méconnaissance des subtilités du sujet

Histoire

Suppression de données temporaires dans la base de données de production avec TRUNCATE au lieu de TEST. Le déclencheur d'audit pour la suppression de lignes n'a fonctionné que sur DELETE — les informations sur les données perdues, le moment et l'utilisateur sont absentes. Cela a compliqué l'analyse de l'incident.

Histoire

Nettoyage de la table avec TRUNCATE en présence d'une clé étrangère. SQL a renvoyé une erreur en raison de la contrainte d'intégrité référentielle, le script n'a pas abouti, l'automatisation s'est arrêtée. Solution : retirer temporairement les clés étrangères ou utiliser DELETE.

Histoire

Suppression massive de données via DELETE dans de grandes tables sans traitement par lots : en raison de longs verrous, la base "s'est figée", d'autres transactions ont attendu la libération des verrous. Résultat — arrêt du système. Correct : utiliser DELETE LIMIT/OFFSET, si pris en charge, ou des lots.