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

Apprenez comment le TRONQUER et EFFACER fonctionnent dans SQL, les différences entre elles et quand vous devriez préférer utiliser l'une plutôt que l'autre.

Lorsque vous travaillez avec des tables de base de données, vous devrez peut-être 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 selon le cas d'utilisation.

Dans ce didacticiel, nous allons examiner de plus près chacune des instructions, comprendre leur fonctionnement et décider quand préférer 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 : 

• Data Definition Language (DDL) Les instructions sont utilisées pour créer et gérer des objets de base de données tels que des tables. Le SQL CREATE, DROPet au TRUNCATE Les instructions sont des exemples d'instructions DDL.
• Manipulation de données Langue (DML) Les instructions sont utilisées pour manipuler des données dans des objets de 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.
• Langage de requête de données (DQL) Les instructions sont utilisées pour récupérer des données à partir de tables de base de données. Tous SELECT les instructions relèvent du sous-ensemble DQL.

How to Use the SQL TRUNCATE Statement

Syntaxe de l'instruction SQL TRUNCATE

La syntaxe pour utiliser l'instruction SQL TRUNCATE est la suivante :

TRUNCATE TABLE table_name;

L'exécution de la commande TRUNCATE ci-dessus supprime tous les lignes du tableau spécifié par table_name— et ne supprime pas la table.

L'opération de troncation n'analyse pas tous les enregistrements de la table. C'est donc raisonnablement plus rapide lorsque vous travaillez avec de grandes tables de base de données.

SQL TRUNCATE Exemple d'utilisation

📑 Notes: Si tu as MySQL installé sur votre machine, vous pouvez coder en utilisant le client de ligne de commande MySQL. Vous pouvez également suivre 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;
Query OK, 1 row affected (1.50 sec)

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

mysql> use db1;
Database changed

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

-- Create the tasks table
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',
    assignee VARCHAR(100)
);

Dans cet exemple, le tasks table comporte les colonnes suivantes :

• task_id: Un identifiant unique qui s'incrémente automatiquement pour chaque tâche.
• title: Le titre ou le nom de la tâche, limité à 255 caractères.
• due_date: date d'échéance de la tâche, représentée sous forme de date.
• status: Le statut de la tâche, qui peut être 'En attente', 'En cours' ou 'Terminé'. La valeur par défaut est définie sur "En attente".
• assignee: le responsable de la tâche particulière.

Maintenant que nous avons créé le tasks table, insérons-y des enregistrements :

-- Inserting multiple records into the tasks table
INSERT INTO tasks (title, due_date, status, assignee)
VALUES
    ('Task 1', '2023-08-10', 'Pending', 'John'),
    ('Task 2', '2023-08-11', 'In Progress', 'Jane'),
    ('Task 3', '2023-08-12', 'Completed', 'Mike'),
    ('Task 4', '2023-08-13', 'Pending', 'Alice'),
    ('Task 5', '2023-08-14', 'In Progress', 'Bob'),
    ('Task 6', '2023-08-15', 'Completed', 'Emily'),
    ('Task 7', '2023-08-16', 'Pending', 'David'),
    ('Task 8', '2023-08-17', 'In Progress', 'Olivia'),
    ('Task 9', '2023-08-18', 'Pending', 'Daniel'),
    ('Task 10', '2023-08-19', 'Completed', 'Sophia'),
    ('Task 11', '2023-08-20', 'Pending', 'Matthew'),
    ('Task 12', '2023-08-21', 'In Progress', 'Ava'),
    ('Task 13', '2023-08-22', 'Completed', 'William'),
    ('Task 14', '2023-08-23', 'Pending', 'Ella'),
    ('Task 15', '2023-08-24', 'In Progress', 'James'),
    ('Task 16', '2023-08-25', 'Completed', 'Lily'),
    ('Task 17', '2023-08-26', 'Pending', 'Benjamin'),
    ('Task 18', '2023-08-27', 'In Progress', 'Mia'),
    ('Task 19', '2023-08-28', 'Pending', 'Henry'),
    ('Task 20', '2023-08-29', 'Completed', 'Isabella');

Lors de l'exécution de l'instruction d'insertion, vous devriez voir une sortie similaire :

Query OK, 20 rows affected (0.18 sec)
Records: 20  Duplicates: 0  Warnings: 0

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

TRUNCATE TABLE tasks;
Query OK, 0 rows affected (0.72 sec)

Cela supprime tous les enregistrements et non la table. Vous pouvez le vérifier en exécutant SHOW TABLES; ainsi:

SHOW TABLES;

+---------------+
| Tables_in_db1 |
+---------------+
| tasks         |
+---------------+
1 row in set (0.00 sec)

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

SELECT * FROM tasks;
Empty set (0.00 sec)

How to Use the SQL DELETE Statement

Syntaxe de l'instruction SQL DELETE

La syntaxe générale pour utiliser l'instruction SQL DELETE est la suivante :

DELETE FROM table_name 
WHERE condition;

La condition dans la clause WHERE est le prédicat qui détermine qui des lignes doivent être supprimées. L'instruction DELETE supprime toutes les lignes pour lesquelles le prédicat est True.

L'instruction DELETE vous permet donc d'avoir plus de contrôle sur les enregistrements à supprimer.

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

DELETE FROM table_name;

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

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

SQL DELETE Exemple d'utilisation

Voyons maintenant l'instruction de suppression SQL en action.

Nous avons supprimé tous les enregistrements du tasks tableau. Vous pouvez donc réexécuter l'instruction INSERT (que nous avons exécutée précédemment) pour insérer des enregistrements :

-- Inserting multiple records into the tasks table
INSERT INTO tasks (title, due_date, status, assignee)
VALUES
    ('Task 1', '2023-08-10', 'Pending', 'John'),
    ('Task 2', '2023-08-11', 'In Progress', 'Jane'),
    ('Task 3', '2023-08-12', 'Completed', 'Mike'),
    ...
    ('Task 18', '2023-08-27', 'In Progress', 'Mia'),
    ('Task 19', '2023-08-28', 'Pending', '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';
Query OK, 6 rows affected (0.14 sec)

Exécutez maintenant cette requête SELECT :

SELECT * FROM tasks;

Vous verrez qu'il y a actuellement 14 lignes :

+---------+---------+------------+-------------+----------+
| task_id | title   | due_date   | status      | assignee |
+---------+---------+------------+-------------+----------+
|       1 | Task 1  | 2023-08-10 | Pending     | John     |
|       2 | Task 2  | 2023-08-11 | In Progress | Jane     |
|       4 | Task 4  | 2023-08-13 | Pending     | Alice    |
|       5 | Task 5  | 2023-08-14 | In Progress | Bob      |
|       7 | Task 7  | 2023-08-16 | Pending     | David    |
|       8 | Task 8  | 2023-08-17 | In Progress | Olivia   |
|       9 | Task 9  | 2023-08-18 | Pending     | Daniel   |
|      11 | Task 11 | 2023-08-20 | Pending     | Matthew  |
|      12 | Task 12 | 2023-08-21 | In Progress | Ava      |
|      14 | Task 14 | 2023-08-23 | Pending     | Ella     |
|      15 | Task 15 | 2023-08-24 | In Progress | James    |
|      17 | Task 17 | 2023-08-26 | Pending     | Benjamin |
|      18 | Task 18 | 2023-08-27 | In Progress | Mia      |
|      19 | Task 19 | 2023-08-28 | Pending     | Henry    |
+---------+---------+------------+-------------+----------+
14 rows in set (0.00 sec)

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

DELETE FROM tasks;
Query OK, 14 rows affected (0.20 sec)

Et la table des tâches est maintenant vide :

SELECT * FROM tasks;
Empty set (0.00 sec)

The SQL DROP Statement

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 le DROP TABLE commande comme ceci :

DROP TABLE table_name;

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

mysql> DROP TABLE tasks;
Query OK, 0 rows affected (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;
Empty set (0.00 sec)

When to Use TRUNCATE vs. DELETE in SQL

Pour résumer:

• 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 de lignes en fonction de conditions spécifiques, utilisez l'instruction DELETE.

résumer

Terminons notre discussion par un résumé :

• Lorsque vous travaillez avec des tables de base de données, vous souhaiterez peut-être supprimer le 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 prend la syntaxe : TRUNCATE TABLE table_name;. Il supprime toutes les lignes de la table spécifiée par table_name mais ne supprime pas la table elle-même.
• L'instruction DELETE prend la syntaxe : DELETE FROM table_name WHERE condition;. Cela supprime les lignes pour lesquelles le prédicat condition est vrai.
• Exécution de l'instruction SQL DELETE sans compromis. la clause WHERE supprime toutes les lignes de la table. Ainsi, d'un point de vue fonctionnel, cela donne le même résultat que l'instruction SQL TRUNCATE.
• L'exécution de TRUNCATE est particulièrement rapide lorsque vous travaillez avec des tables plus grandes, car elle n'analyse pas la table entière. Ainsi, lorsque vous devez supprimer toutes les lignes d'une grande table de base de données, l'exécution de truncate peut être 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 couramment utilisées, consultez ceci Aide-mémoire SQL.