TRUNCATE vs. DELETE en SQL : Comprendre les différences

Découvrez le fonctionnement des instructions TRUNCATE et DELETE en SQL, leurs différences et les cas dans lesquels vous devez préférer l’une à l’autre.

Lorsque vous travaillez avec des tables de base de données, vous pouvez être amené à supprimer un sous-ensemble de lignes ou toutes les lignes. Pour supprimer des lignes d’une table de base de données, vous pouvez utiliser les instructions SQL TRUNCATE ou DELETE, en fonction du cas d’utilisation.

Dans ce tutoriel, nous allons examiner de plus près chacune de ces instructions, comprendre leur fonctionnement et décider quand il est préférable d’utiliser TRUNCATE plutôt que DELETE et vice versa.

Avant d’aller plus loin, il est utile de passer en revue les sous-ensembles SQL suivants :

Les instructionsDDL (Data Definition Language) sont utilisées pour créer et gérer des objets de base de données tels que les tables. Les instructions SQL CREATE, DROP et TRUNCATE sont des exemples d’instructions DDL.
Les instructions DML(Data Manipulation Language ) sont utilisées pour manipuler les données au sein des objets de la base de données. Les instructions DML sont utilisées pour effectuer les opérations de création, de lecture, de mise à jour et de suppression d’enregistrements.
Les instructionsDQL (Data Query Language) sont utilisées pour extraire des données des tables de la base de données. Toutes les instructions SELECT font partie du sous-ensemble DQL.

Comment utiliser l’instruction SQL TRUNCATE ?

Syntaxe de l’instruction SQL TRUNCATE

La syntaxe de l’instruction SQL TRUNCATE est la suivante :

TRUNCATE TABLE nom_de_la_table ;

L’exécution de la commande TRUNCATE ci-dessus supprime toutes les lignes de la table spécifiée par nom_table, maisne supprime pas la table.

L’opération de troncature n’analyse pas tous les enregistrements de la table. Elle est donc raisonnablement plus rapide lorsque vous travaillez avec des tables de base de données volumineuses.

Exemple d’utilisation de SQL TRUNCATE

📑 Note: Si MySQL est installé sur votre machine, vous pouvez coder en utilisant le client de ligne de commande MySQL. Vous pouvez également suivre le processus dans un autre SGBD de votre choix, tel que PostgreSQL.

Commençons par créer une base de données avec laquelle travailler :

mysql> CREATE DATABASE db1 ;
Requête OK, 1 ligne affectée (1.50 sec)

Sélectionnez ensuite la base de données que nous venons de créer :

mysql> use db1 ;
Modification de la base de données

L’étape suivante consiste à créer une table de base de données. Exécutez l’instruction CREATE TABLE suivante pour créer une table de tâches simple :

-- Créez la table tasks
CREATE TABLE tasks (
    task_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    due_date DATE,
    status ENUM('Pending', 'In Progress', 'Completed') DEFAULT 'Pending',
    assigné VARCHAR(100)
) ;

Dans cet exemple, la table tasks contient les colonnes suivantes :

task_id: Un identifiant unique à incrémentation automatique pour chaque tâche.
title(titre) : Le titre ou le nom de la tâche, limité à 255 caractères.
due_date: la date d’échéance de la tâche, représentée par une date.
status (statut) : Le statut de la tâche, qui peut être “En attente”, “En cours” ou “Terminée”. La valeur par défaut est “En attente”.
assigné: Le destinataire de la tâche en question.

Maintenant que nous avons créé le tableau des tâches, insérons-y des enregistrements :

-- Insertion de plusieurs enregistrements dans la table tasks
INSERT INTO tasks (title, due_date, status, assignee)
VALUES
    ('Tâche 1', '2023-08-10', 'Pending', 'John'),
    ('Task 2', '2023-08-11', 'En cours', 'Jane'),
    ('Tâche 3', '2023-08-12', 'Terminé', 'Mike'),
    ('Tâche 4', '2023-08-13', 'En attente', 'Alice'),
    ('Tâche 5', '2023-08-14', 'En cours', 'Bob'),
    ('Task 6', '2023-08-15', 'Completed', 'Emily'),
    ('Tâche 7', '2023-08-16', 'En attente', 'David'),
    ('Tâche 8', '2023-08-17', 'En cours', 'Olivia'),
    ('Tâche 9', '2023-08-18', 'En attente', 'Daniel'),
    ('Tâche 10', '2023-08-19', 'Terminé', 'Sophia'),
    ('Tâche 11', '2023-08-20', 'En attente', 'Matthew'),
    ('Tâche 12', '2023-08-21', 'En cours', 'Ava'),
    ('Tâche 13', '2023-08-22', 'Terminé', 'William'),
    ('Tâche 14', '2023-08-23', 'En attente', 'Ella'),
    ('Tâche 15', '2023-08-24', 'En cours', 'James'),
    ('Tâche 16', '2023-08-25', 'Terminé', 'Lily'),
    ('Tâche 17', '2023-08-26', 'En attente', 'Benjamin'),
    ('Tâche 18', '2023-08-27', 'En cours', 'Mia'),
    ('Tâche 19', '2023-08-28', 'En attente', 'Henry'),
    ('Task 20', '2023-08-29', 'Completed', 'Isabella') ;

Lorsque vous exécutez l’instruction d’insertion, vous devriez obtenir un résultat similaire :

Requête OK, 20 lignes affectées (0.18 sec)
Enregistrements : 20 Duplicates : 0 Avertissements : 0

Exécutez maintenant la commande TRUNCATE table pour supprimer tous les enregistrements de la table tasks:

TRUNCATE TABLE tasks ;
Requête OK, 0 ligne affectée (0.72 sec)

Cette opération supprime tous les enregistrements et non la table. Vous pouvez le vérifier en exécutant la commande SHOW TABLES ; comme suit :

SHOW TABLES ;

 --------------- 
| Tables_in_db1
 --------------- 
| tâches |
 --------------- 
1 ligne dans l'ensemble (0.00 sec)

Et une requête SELECT pour récupérer les données de la table tasks renvoie un ensemble vide :

SELECT * FROM tâches ;
Ensemble vide (0.00 sec)

Comment utiliser l’instruction SQL DELETE

A woman standing in front of a blackboard explaining the differences between truncate and delete SQL statements.

Syntaxe de l’instruction SQL DELETE

La syntaxe générale de l’instruction SQL DELETE est la suivante :

DELETE FROM nom_table 
Condition WHERE ;

La condition de la clause WHERE est le prédicat qui détermine quelles lignes doivent être supprimées. L’instruction DELETE supprime toutes les lignes pour lesquelles le prédicat est Vrai.

L’instruction DELETE vous permet donc de mieux contrôler les enregistrements à supprimer.

Mais que se passe-t-il lorsque vous utilisez l’instruction DELETE sans la clause WHERE ?

DELETE FROM nom_table ;

L’exécution de l’instruction DELETE comme indiqué supprime toutes les lignes de la table de la base de données.

Si une instruction DELETE ou un ensemble d’instructions DELETE fait partie d’une transaction non validée, vous pouvez annuler les modifications. Toutefois, il est recommandé de sauvegarder vos données ailleurs.

Exemple d’utilisation de SQL DELETE

Voyons maintenant l’utilisation de l’instruction SQL DELETE.

Nous avons supprimé tous les enregistrements de la table tasks. Vous pouvez donc réexécuter l’instruction INSERT (que nous avons exécutée plus tôt) pour insérer des enregistrements :

-- Insertion de plusieurs enregistrements dans la table tasks
INSERT INTO tasks (title, due_date, status, assignee)
VALUES
    ('Tâche 1', '2023-08-10', 'Pending', 'John'),
    ('Task 2', '2023-08-11', 'En cours', 'Jane'),
    ('Task 3', '2023-08-12', 'Completed', 'Mike'),
    ...
    ('Tâche 18', '2023-08-27', 'En cours', 'Mia'),
    ('Tâche 19', '2023-08-28', 'En cours', 'Henry'),
    ('Task 20', '2023-08-29', 'Completed', 'Isabella') ;

Commençons par utiliser l’instruction DELETE avec la clause WHERE. La requête suivante supprime toutes les lignes dont le statut est “Completed” :

DELETE FROM tasks WHERE status = 'Completed' ;
Requête OK, 6 lignes affectées (0.14 sec)

Exécutez maintenant cette requête SELECT :

SELECT * FROM tasks ;

Vous verrez qu’il y a actuellement 14 lignes :

 --------- --------- ------------ ------------- ---------- 
| Tâches - ID de tâche - Titre - Date d'échéance - Statut - Destinataire - ID de tâche - Titre - Date d'échéance - Statut - Destinataire - ID de tâche - Titre - Statut - Destinataire
 --------- --------- ------------ ------------- ---------- 
| 1 | Tâche 1 | 2023-08-10 | En attente | John |
| 2 | Tâche 2 | 2023-08-11 | En cours | Jane |
| 4 | Tâche 4 | 2023-08-13 | En attente | Alice |
| 5 | Tâche 5 | 2023-08-14 | En cours | Bob |
| 7 | Tâche 7 | 2023-08-16 | En attente | David |
| 8 | Tâche 8 | 2023-08-17 | En cours | Olivia |
| 9 | Tâche 9 | 2023-08-18 | En attente | Daniel
| 11 | Tâche 11 | 2023-08-20 | En attente | Matthew
| 12 | Tâche 12 | 2023-08-21 | En cours | Ava |
| 14 | Tâche 14 | 2023-08-23 | En attente | Ella
| 15 | Tâche 15 | 2023-08-24 | En cours | James
| 17 | Tâche 17 | 2023-08-26 | En cours | Benjamin |
| 18 | Tâche 18 | 2023-08-27 | En cours | Mia |
| 19 | Tâche 19 | 2023-08-28 | En attente | Henry |
 --------- --------- ------------ ------------- ---------- 
14 lignes dans le jeu (0.00 sec)

L’exécution de l’instruction DELETE suivante supprime les 14 enregistrements restants dans la table :

DELETE FROM tasks ;
Requête OK, 14 lignes affectées (0.20 sec)

Le tableau des tâches est maintenant vide :

SELECT * FROM tasks ;
Ensemble vide (0.00 sec)

L’instruction SQL DROP

Jusqu’à présent, nous avons appris :

L’instruction TRUNCATE supprime toutes les lignes de la table.
L’instruction DELETE, sans clause WHERE, supprime tous les enregistrements de la table.

Cependant, les instructions TRUNCATE et DELETE ne suppriment pas la table. Si vous souhaitez supprimer la table de la base de données, vous pouvez utiliser la commande DROP TABLE comme suit :

DROP TABLE nom_table ;

Supprimons maintenant la table tasks de la base de données :

mysql> DROP TABLE tâches ;
Requête OK, 0 ligne affectée (0.43 sec)

Vous verrez que SHOW TABLES ; renvoie un ensemble vide (puisque nous avons supprimé la seule table présente dans la base de données) :

mysql> SHOW TABLES ;
Jeu vide (0.00 sec)

Quand utiliser TRUNCATE vs. DELETE en SQL

Fonctionnalité	TRUNCATE	EFFACER
Syntaxe	`TRUNCATE TABLE nom_table ;`	Avec la clause WHERE : `DELETE FROM nom_table WHERE condition ;` Sans clause WHERE : `DELETE TABLE nom_table ;`
Sous-ensemble SQL	Langage de définition des données (DDL)	Langage de manipulation des données (DML)
Effet	Supprime toutes les lignes de la table de la base de données.	Lorsqu’elle est exécutée sans la clause WHERE, l’instruction DELETE supprime tous les enregistrements de la table de la base de données.
Performance	Plus efficace que l’instruction DELETE lorsque vous travaillez avec de grandes tables.	Moins efficace que l’instruction TRUNCATE.

En résumé :

Lorsque vous devez supprimer toutes les lignes d’une grande table de base de données, utilisez l’instruction TRUNCATE.
Pour supprimer un sous-ensemble d’enregistrements en fonction de conditions spécifiques, utilisez l’instruction DELETE.

Récapitulation

Concluons notre discussion par un résumé :

Lorsque vous travaillez avec des tables de base de données, vous pouvez souhaiter supprimer un sous-ensemble de lignes ou toutes les lignes d’une table particulière. Pour ce faire, vous pouvez utiliser les instructions TRUNCATE ou DELETE.
L’instruction TRUNCATE a la syntaxe suivante TRUNCATE TABLE nom_table ;. Elle supprime toutes les lignes de la table spécifiée par nom_table, mais ne supprime pas la table elle-même.
L’instruction DELETE a la syntaxe suivante : DELETE FROM nom_table DELETE FROM nom_table WHERE condition ;. Elle supprime les lignes pour lesquelles la condition prédicat est vraie.
L’exécution de l’instruction SQL DELETE sans la clause WHERE supprime toutes les lignes de la table. D’un point de vue fonctionnel, le résultat est donc le même que celui de l’instruction SQL TRUNCATE.
L’exécution de l’instruction TRUNCATE est particulièrement rapide lorsque vous travaillez avec des tables de grande taille, car elle n’analyse pas l’ensemble de la table. Ainsi, lorsque vous devez supprimer toutes les lignes d’une grande table de base de données, l’exécution de l’instruction TRUNCATE peut s’avérer plus efficace.
Lorsque vous devez supprimer un sous-ensemble de lignes, en fonction d’une condition spécifique, vous pouvez utiliser l’instruction SQL DELETE.

Pour un examen rapide des commandes SQL les plus utilisées, consultez l’aide-mémoire SQL.

Bala Priya C
Contributeur
Bala Priya est une développeuse et rédactrice technique indienne qui a plus de trois ans d’expérience dans la rédaction de contenu technique. Elle partage ses connaissances avec la communauté des développeurs en rédigeant des tutoriels techniques, des guides pratiques, etc. Elle travaille actuellement comme rédactrice technique chez Geekflare. Elle contribue également activement à plusieurs autres publications techniques telles que freeCodeCamp et KDnuggets.