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

Explorez le fonctionnement des commandes TRUNCATE et DELETE en SQL, leurs distinctions et les contextes où l'une est préférable à l'autre.

Lors de la manipulation de tables de bases de données, vous pourriez être amené à effacer un groupe spécifique de lignes ou la totalité des données. Pour cela, les instructions SQL TRUNCATE et DELETE sont à votre disposition, chacune s'adaptant à des scénarios précis.

Ce guide approfondira ces deux instructions, en expliquant leur mécanique et en déterminant dans quels cas privilégier TRUNCATE ou DELETE, et vice-versa.

Avant de poursuivre, il est pertinent de revoir les catégories d'instructions SQL :

• Les instructions DDL (Data Definition Language) servent à structurer et administrer les objets de bases de données, comme les tables. Les commandes CREATE, DROP et TRUNCATE en sont des exemples.
• Les instructions DML (Data Manipulation Language) permettent la manipulation des données au sein des objets de la base. Les opérations de création, lecture, mise à jour et suppression d'enregistrements sont exécutées via les DML.
• Les instructions DQL (Data Query Language) sont dédiées à la récupération de données depuis les tables. Toutes les commandes SELECT font partie de ce groupe.

Comment utiliser la commande SQL TRUNCATE

Syntaxe de la commande SQL TRUNCATE

Voici la syntaxe pour l'utilisation de l'instruction SQL TRUNCATE :

TRUNCATE TABLE nom_de_la_table;

L'exécution de cette commande supprime toutes les lignes de la table spécifiée, sans pour autant supprimer la table elle-même.

L'opération TRUNCATE n'analyse pas chaque enregistrement, ce qui la rend significativement plus rapide lors de la manipulation de grandes tables.

Exemple d'utilisation de SQL TRUNCATE

📝 Note : Si MySQL est installé sur votre machine, vous pouvez utiliser son interface en ligne de commande. Vous pouvez également utiliser d'autres SGBD tels que PostgreSQL.

Commençons par créer une base de données de test :

mysql> CREATE DATABASE bd1;
Query OK, 1 ligne affectée (1.50 sec)

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

mysql> use bd1;
Database changed

L'étape suivante est la création d'une table. Exécutez la commande CREATE TABLE pour créer une table de tâches simple :

-- Création de la table des tâches
CREATE TABLE taches (
    id_tache INT AUTO_INCREMENT PRIMARY KEY,
    titre VARCHAR(255) NOT NULL,
    date_echeance DATE,
    statut ENUM('En attente', 'En cours', 'Terminé') DEFAULT 'En attente',
    assigne VARCHAR(100)
);

Dans cet exemple, la table des tâches contient les colonnes suivantes :

• id_tache : un identifiant unique auto-incrémenté pour chaque tâche.
• titre : le titre de la tâche, limité à 255 caractères.
• date_echeance : la date d'échéance de la tâche.
• statut : le statut de la tâche ('En attente', 'En cours' ou 'Terminé'), avec 'En attente' par défaut.
• assigne : la personne assignée à la tâche.

Insérons maintenant des données dans la table des tâches :

-- Insertion de plusieurs enregistrements dans la table des tâches
INSERT INTO taches (titre, date_echeance, statut, assigne)
VALUES
    ('Tâche 1', '2023-08-10', 'En attente', 'John'),
    ('Tâche 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'),
    ('Tâche 6', '2023-08-15', 'Terminé', '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'),
    ('Tâche 20', '2023-08-29', 'Terminé', 'Isabella');

Suite à l'exécution de cette commande INSERT, vous devriez obtenir un résultat similaire :

Query OK, 20 lignes affectées (0.18 sec)
Enregistrements: 20  Doublons: 0  Avertissements: 0

Exécutez maintenant la commande TRUNCATE pour vider la table des tâches :

TRUNCATE TABLE taches;
Query OK, 0 lignes affectées (0.72 sec)

Cette action supprime tous les enregistrements, mais laisse la table intacte. Vous pouvez le vérifier en utilisant SHOW TABLES ; :

SHOW TABLES;

+----------------+
| Tables_in_bd1  |
+----------------+
| taches          |
+----------------+
1 ligne dans l'ensemble (0.00 sec)

Une requête SELECT sur la table des tâches renverra un ensemble vide :

SELECT * FROM taches;
Ensemble vide (0.00 sec)

Comment utiliser l'instruction SQL DELETE

Syntaxe de l'instruction SQL DELETE

La syntaxe générale de la commande SQL DELETE est la suivante :

DELETE FROM nom_de_la_table 
WHERE condition;

La clause WHERE spécifie les lignes qui doivent être supprimées. Seules les lignes pour lesquelles la condition est vraie seront affectées.

L'instruction DELETE offre un contrôle plus précis sur les enregistrements à supprimer.

Mais qu'arrive-t-il lorsque l'instruction DELETE est utilisée sans la clause WHERE ? 🤔

DELETE FROM nom_de_la_table;

Dans ce cas, toutes les lignes de la table sont supprimées.

Si une instruction DELETE (ou une série d'instructions) fait partie d'une transaction non validée, vous pouvez annuler les modifications. Il est cependant recommandé de sauvegarder vos données au préalable.

Exemple d'utilisation de SQL DELETE

Mettons maintenant l'instruction DELETE en pratique.

Nous avons précédemment vidé la table des tâches. Vous pouvez réexécuter l'instruction INSERT pour la remplir à nouveau :

-- Insertion de plusieurs enregistrements dans la table des tâches
INSERT INTO taches (titre, date_echeance, statut, assigne)
VALUES
    ('Tâche 1', '2023-08-10', 'En attente', 'John'),
    ('Tâche 2', '2023-08-11', 'En cours', 'Jane'),
    ('Tâche 3', '2023-08-12', 'Terminé', 'Mike'),
    ...
    ('Tâche 18', '2023-08-27', 'En cours', 'Mia'),
    ('Tâche 19', '2023-08-28', 'En attente', 'Henry'),
    ('Tâche 20', '2023-08-29', 'Terminé', 'Isabella');

Commençons par utiliser l'instruction DELETE avec la clause WHERE. La requête suivante supprimera toutes les lignes ayant le statut 'Terminé' :

DELETE FROM taches WHERE statut="Terminé";
Query OK, 6 lignes affectées (0.14 sec)

Exécutez maintenant cette requête SELECT :

SELECT * FROM taches;

Vous remarquerez qu'il reste 14 lignes :

+----------+----------+------------+------------+----------+
| id_tache | titre    | date_echeance | statut    | assigne  |
+----------+----------+------------+------------+----------+
|       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 attente  | Benjamin |
|      18  | Tâche 18 | 2023-08-27 | En cours    | Mia      |
|      19  | Tâche 19 | 2023-08-28 | En attente  | Henry    |
+----------+----------+------------+------------+----------+
14 lignes dans l'ensemble (0.00 sec)

L'exécution de l'instruction DELETE suivante supprimera les 14 lignes restantes de la table :

DELETE FROM taches;
Query OK, 14 lignes affectées (0.20 sec)

La table est maintenant vide :

SELECT * FROM taches;
Ensemble vide (0.00 sec)

L'instruction SQL DROP

Nous avons vu jusqu'à présent que :

• L'instruction TRUNCATE supprime toutes les lignes d'une table.
• L'instruction DELETE (sans clause WHERE) supprime également tous les enregistrements.

Cependant, TRUNCATE et DELETE ne suppriment pas la table. Pour supprimer une table de la base de données, utilisez la commande DROP TABLE :

DROP TABLE nom_de_la_table;

Supprimons maintenant la table des tâches de la base de données :

mysql> DROP TABLE taches;
Query OK, 0 lignes affectées (0.43 sec)

Vous constaterez que SHOW TABLES; renvoie un ensemble vide (car nous avons supprimé la seule table de la base de données) :

mysql> SHOW TABLES;
Ensemble vide (0.00 sec)

Quand utiliser TRUNCATE ou DELETE en SQL

En résumé :

• Utilisez TRUNCATE pour supprimer toutes les lignes d'une grande table.
• Utilisez DELETE pour supprimer un sous-ensemble de lignes en fonction de conditions précises.

Résumé

Terminons notre discussion par un récapitulatif :

• Lors de la manipulation de tables de bases de données, vous pourriez vouloir supprimer une partie ou la totalité des données d'une table. Pour cela, vous pouvez recourir aux instructions TRUNCATE ou DELETE.
• L'instruction TRUNCATE utilise la syntaxe : TRUNCATE TABLE nom_table ;. Elle supprime toutes les lignes d'une table spécifiée, mais n'efface pas la table elle-même.
• L'instruction DELETE utilise la syntaxe : DELETE FROM nom_table WHERE condition ;. Elle supprime les lignes pour lesquelles la condition est vraie.
• L'exécution de la commande DELETE sans la clause WHERE supprime toutes les lignes d'une table. D'un point de vue fonctionnel, c'est équivalent à l'instruction TRUNCATE.
• TRUNCATE est particulièrement rapide pour les grandes tables, car elle n'analyse pas la totalité de la table. L'utilisation de TRUNCATE peut être plus efficace si vous devez supprimer toutes les lignes d'une grande table.
• Si vous souhaitez supprimer un sous-ensemble de lignes, selon une condition particulière, utilisez l'instruction DELETE.

Pour une référence rapide sur les commandes SQL les plus utilisées, consultez ce guide de poche SQL.