В каких случаях предпочтительнее использовать инструкцию truncate table а не delete

Узнайте, как операторы TRUNCATE и DELETE работают в SQL, в чем разница между ними и когда лучше использовать один, а не другой.

При работе с таблицами базы данных может потребоваться удалить подмножество строк или все строки. Чтобы удалить строки из таблицы базы данных, вы можете использовать инструкции SQL TRUNCATE или DELETE в зависимости от варианта использования.

В этом руководстве мы подробно рассмотрим каждое из операторов, поймем, как они работают, и решим, когда лучше использовать TRUNCATE вместо DELETE и наоборот.

Прежде чем мы двинемся дальше, полезно рассмотреть следующие подмножества SQL:

• Операторы языка определения данных (DDL) используются для создания объектов базы данных, таких как таблицы, и управления ими. Операторы SQL CREATE, DROP и TRUNCATE являются примерами операторов DDL.
• Операторы языка манипулирования данными (DML) используются для управления данными в объектах базы данных. Операторы DML используются для выполнения операций создания, чтения, обновления и удаления записей.
• Операторы языка запросов данных (DQL) используются для извлечения данных из таблиц базы данных. Все операторы SELECT относятся к подмножеству DQL.

Синтаксис оператора SQL TRUNCATE

Синтаксис использования оператора SQL TRUNCATE следующий:

TRUNCATE TABLE table_name;

Выполнение приведенной выше команды TRUNCATE удаляет все строки в таблице, указанной в table_name, но не удаляет таблицу.

Операция усечения не сканирует все записи в таблице. Таким образом, при работе с большими таблицами базы данных это происходит быстрее.

Пример использования SQL TRUNCATE

📑 Примечание: если у вас есть MySQL установлен на вашем компьютере, вы можете кодировать с помощью клиента командной строки MySQL. Вы также можете следовать в другой СУБД по вашему выбору, такой как PostgreSQL.

Давайте сначала создадим базу данных для работы с:

mysql> CREATE DATABASE db1;
Query OK, 1 row affected (1.50 sec)

Затем выберите базу данных, которую мы только что создали:

mysql> use db1;
Database changed

Следующим шагом является создание таблицы базы данных. Запустите следующую инструкцию CREATE 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)
);

В этом примере таблица задач имеет следующие столбцы:

• task_id: автоматически увеличивающийся уникальный идентификатор для каждой задачи.
• title: Название или название задачи, не более 255 символов.
• due_date: срок выполнения задачи, представленный в виде даты.
• статус: статус задачи, который может быть «Ожидание», «Выполняется» или «Завершено». По умолчанию установлено значение «Ожидание».
• правопреемник: правопреемник для конкретной задачи.

Теперь, когда мы создали таблицу tasks, давайте вставим в нее записи:

-- 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');

После запуска оператора вставки вы должны увидеть аналогичный вывод:

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

Теперь запустите команду TRUNCATE table, чтобы удалить все записи из таблицы tasks:

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

При этом удаляются все записи, а не таблица. Вы можете убедиться в этом, запустив SHOW TABLES; вот так:

SHOW TABLES;

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

И запрос SELECT для извлечения данных из таблицы tasks возвращает пустой набор:

SELECT * FROM tasks;
Empty set (0.00 sec)

Как использовать оператор SQL DELETE

Синтаксис оператора SQL DELETE

Общий синтаксис для использования оператора SQL DELETE следующий:

DELETE FROM table_name 
WHERE condition;

Условие в предложении WHERE — это предикат, определяющий, какие строки следует удалить. Оператор DELETE удаляет все строки, для которых предикат имеет значение True.

Таким образом, оператор DELETE позволяет лучше контролировать, какие записи удаляются.

Но что происходит, когда вы используете оператор DELETE без предложения WHERE?🤔

DELETE FROM table_name;

Выполнение оператора DELETE, как показано, удаляет все строки в таблице базы данных.

Если оператор DELETE или набор операторов DELETE являются частью незафиксированной транзакции, вы можете отменить изменения. Тем не менее, рекомендуется сделать резервную копию ваших данных в другом месте.

Пример использования SQL DELETE

Теперь давайте посмотрим на оператор удаления SQL в действии.

Мы удалили все записи из таблицы tasks. Таким образом, вы можете повторно запустить инструкцию INSERT (которую мы запускали ранее) для вставки записей:

-- 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');

Сначала воспользуемся оператором DELETE с предложением WHERE. Следующий запрос удаляет все строки со статусом «Завершено»:

DELETE FROM tasks WHERE status="Completed";
Query OK, 6 rows affected (0.14 sec)

Теперь запустите этот запрос SELECT:

SELECT * FROM tasks;

Вы увидите, что в настоящее время имеется 14 строк:

+---------+---------+------------+-------------+----------+
| 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)

Выполнение следующего оператора DELETE удаляет все оставшиеся 14 записей в таблице:

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

И таблица задач теперь пуста:

SELECT * FROM tasks;
Empty set (0.00 sec)

Оператор SQL DROP

На данный момент мы узнали:

• Оператор TRUNCATE удаляет все строки из таблицы.
• Оператор DELETE — без предложения WHERE — удаляет все записи из таблицы.

Однако операторы TRUNCATE и DELETE не удаляют таблицу. Если вы хотите удалить таблицу из базы данных, вы можете использовать команду DROP TABLE следующим образом:

DROP TABLE table_name;

Теперь давайте удалим таблицу tasks из базы данных:

mysql> DROP TABLE tasks;
Query OK, 0 rows affected (0.43 sec)

Вы увидите, что SHOW TABLES; возвращает пустой набор (поскольку мы удалили единственную таблицу, которая присутствовала в базе данных):

mysql> SHOW TABLES;
Empty set (0.00 sec)

Когда использовать TRUNCATE или DELETE в SQL

ФункцияTRUNCATEDELETESСинтаксисTRUNCATE TABLE имя_таблицы; с условием WHERE: DELETE FROM имя_таблицы ГДЕ условие;
Без предложения WHERE: DELETE TABLE имя_таблицы;Подмножество SQLЯзык определения данных (DDL)Язык манипулирования данными (DML)ЭффектУдаляет все строки в таблице базы данных.При запуске без предложения WHERE оператор DELETE удаляет все записи в таблице базы данных.ПроизводительностьБольше эффективнее оператора DELETE при работе с большими таблицами. Менее эффективен, чем оператор TRUNCATE.

Подводить итоги:

• Если вам нужно удалить все строки из большой таблицы базы данных, используйте оператор TRUNCATE.
• Чтобы удалить подмножество строк на основе определенных условий, используйте оператор DELETE.

Подводя итоги

Завершим нашу дискуссию подведением итогов:

• При работе с таблицами базы данных может потребоваться удалить подмножество строк или все строки в определенной таблице. Для этого вы можете использовать операторы TRUNCATE или DELETE.
• Оператор TRUNCATE имеет синтаксис: TRUNCATE TABLE имя_таблицы;. Он удаляет все строки в таблице, указанной в table_name, но не удаляет саму таблицу.
• Оператор DELETE имеет следующий синтаксис: DELETE FROM имя_таблицы WHERE условие;. Это удаляет строки, для которых условие предиката истинно.
• Выполнение инструкции SQL DELETE без предложения WHERE удаляет все строки в таблице. Таким образом, функционально это дает тот же результат, что и оператор SQL TRUNCATE.
• Запуск TRUNCATE особенно быстрее при работе с большими таблицами, так как он не сканирует всю таблицу. Поэтому, когда вам нужно удалить все строки в большой таблице базы данных, запуск truncate может быть более эффективным.
• Когда вам нужно удалить подмножество строк — на основе определенного условия — вы можете использовать оператор SQL DELETE.

Для быстрого ознакомления с часто используемыми командами SQL ознакомьтесь с этой памяткой по SQL.

Here is a summary of some important differences between these sql commands:

sql truncate command:

1) It is a DDL (Data Definition Language) command, therefore commands such as COMMIT and ROLLBACK do not apply to this command (the exceptions here are PostgreSQL and MSSQL, whose implementation of the TRUNCATE command allows the command to be used in a transaction)

2) You cannot undo the operation of deleting records, it occurs automatically and is irreversible (except for the above exceptions — provided, however, that the operation is included in the TRANSACTION block and the session is not closed). In case of Oracle — Includes two implicit commits, one before and one after the statement is executed. Therefore, the command cannot be withdrawn while a runtime error will result in commit anyway

3) Deletes all records from the table, records cannot be limited to deletion. For Oracle, when the table is split per partition, individual partitions can be truncated (TRUNCATE) in isolation, making it possible to partially remove all data from the table

4) Frees up the space occupied by the data in the table (in the TABLESPACE — on disk). For Oracle — if you use the REUSE STORAGE clause, the data segments will not be rolled back, i.e. you will keep space from the deleted rows allocated to the table, which can be a bit more efficient if the table is to be reloaded with data. The high mark will be reset

5) TRUNCATE works much faster than DELETE

6) Oracle Flashback in the case of TRUNCATE prevents going back to pre-operative states

7) Oracle — TRUNCATE cannot be granted (GRANT) without using DROP ANY TABLE

The TRUNCATE operation makes unusable indexes usable again

9) TRUNCATE cannot be used when the enabled foreign key refers to another table, then you can:

• execute the command: DROP CONSTRAINT, then TRUNCATE, and then play it through CREATE CONSTRAINT or
• execute the command: SET FOREIGN_KEY_CHECKS = 0; then TRUNCATE, then: SET_FOREIGN_KEY_CHECKS = 1;

10) TRUNCATE requires an exclusive table lock, therefore, turning off exclusive table lock is a way to prevent TRUNCATE operation on the table

11) DML triggers do not fire after executing TRUNCATE (so be very careful in this case, you should not use TRUNCATE, if a delete trigger is defined in the table to perform an automatic table cleanup or a logon action after row deletion). On Oracle, DDL triggers are fired

12) Oracle — TRUNCATE cannot be used in the case of: database link
13) TRUNCATE does not return the number of records deleted

14) Transaction log — one log indicating page deallocation (removes data, releasing allocation of data pages used for storing table data and writes only page deallocations to the transaction log) — faster execution than DELETE. TRUNCATE only needs to adjust the pointer in the database to the table (High Water Mark) and the data is immediately deleted, therefore it uses less system resources and transaction logs

15) Performance (acquired lock) — table and page lock — does not degrade performance during execution

16) TRUNCATE cannot be used on tables involved in transactional replication or merge replication

sql delete command:

1) It is a DML (Data Manipulation Language) command, therefore the following commands are used for this command: COMMIT and ROLLBACK

2) You can undo the operation of removing records by using the ROLLBACK command

3) Deletes all or some records from the table, you can limit the records to be deleted by using the WHERE clause

4) Does not free the space occupied by the data in the table (in the TABLESPACE — on the disk)

5) DELETE works much slower than TRUNCATE

6) Oracle Flashback works for DELETE

7) Oracle — For DELETE, you can use the GRANT command

The DELETE operation does not make unusable indexes usable again

9) DELETE in case foreign key enabled refers to another table, can (or not) be applied depending on foreign key configuration (if not), please:

• execute the command: DROP CONSTRAINT, then TRUNCATE, and then play it through CREATE CONSTRAINT or
• execute the command: SET FOREIGN_KEY_CHECKS = 0; then TRUNCATE, then: SET_FOREIGN_KEY_CHECKS = 1;

10) DELETE requires a shared table lock

11) Triggers fire

12) DELETE can be used in the case of: database link

13) DELETE returns the number of records deleted

14) Transaction log — for each deleted record (deletes rows one at a time and records an entry in the transaction log for each deleted row) — slower execution than TRUNCATE. The table may still contain blank pages after executing the DELETE statement. DELETE needs to read records, check constraints, update block, update indexes, and generate redo / undo. All of this takes time, hence it takes time much longer than with TRUNCATE

15) Performance (acquired lock) — record lock — reduces performance during execution — each record in the table is locked for deletion

16) DELETE can be used on a table used in transactional replication or merge replication