Розберімося, як функціонують оператори TRUNCATE та DELETE у SQL, які їхні ключові відмінності та в яких ситуаціях краще застосовувати той чи інший.
Під час взаємодії з таблицями баз даних часто виникає потреба у вилученні як окремих записів, так і всіх даних. Для видалення рядків з таблиці можна використовувати SQL-оператори TRUNCATE або DELETE, залежно від конкретної задачі.
У цій статті ми детально розглянемо кожен з цих операторів, проаналізуємо їхню роботу та визначимо, коли вигідніше використовувати TRUNCATE замість DELETE і навпаки.
Перш ніж продовжити, корисно пригадати основні підмножини SQL:
- Інструкції мови визначення даних (DDL) використовуються для створення та управління об’єктами бази даних, такими як таблиці. Прикладами DDL-операторів є CREATE, DROP та TRUNCATE.
- Інструкції мови маніпулювання даними (DML) призначені для оперування даними всередині об’єктів бази даних. DML-інструкції застосовуються для виконання операцій створення, читання, оновлення та видалення даних.
- Інструкції мови запитів даних (DQL) слугують для вилучення даних із таблиць. Усі оператори SELECT належать до DQL.
Як використовувати оператор SQL TRUNCATE
Синтаксис оператора 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, щоб створити просту таблицю завдань:
-- Створення таблиці 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',
assignee VARCHAR(100)
);
У цьому прикладі таблиця завдань містить наступні стовпці:
- task_id: унікальний ідентифікатор завдання, що автоматично збільшується.
- title: назва завдання, обмежена 255 символами.
- due_date: дата виконання завдання.
- status: статус завдання, який може набувати значень ‘Pending’ (Очікує), ‘In Progress’ (В процесі) або ‘Completed’ (Виконано). За замовчуванням встановлено значення ‘Pending’.
- assignee: відповідальний за виконання завдання.
Тепер, коли таблиця завдань створена, додамо до неї записи:
-- Вставка множинних записів в таблицю tasks
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 для видалення всіх записів з таблиці завдань:
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 для отримання даних з таблиці завдань поверне пустий набір:
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 в дії.
Ми вже видалили всі записи з таблиці завдань. Тому можна повторно запустити оператор INSERT (який ми використовували раніше), щоб вставити нові записи:
-- Вставка множинних записів в таблицю tasks
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. Наступний запит вилучить всі рядки зі статусом ‘Completed’:
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;
Знищимо тепер таблицю завдань з бази даних:
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
| Функціонал | TRUNCATE | DELETE |
| Синтаксис | TRUNCATE TABLE table_name; | З умовою WHERE: DELETE FROM table_name WHERE умова; Без умови WHERE: DELETE TABLE table_name; |
| SQL-підмножина | Мова визначення даних (DDL) | Мова маніпулювання даними (DML) |
| Ефект | Видаляє всі рядки з таблиці бази даних. | При виконанні без умови WHERE, DELETE видаляє всі записи з таблиці. |
| Продуктивність | Більш ефективний, ніж оператор DELETE при роботі з великими таблицями. | Менш ефективний, ніж оператор TRUNCATE. |
Отже, підсумуємо:
- Якщо потрібно вилучити всі рядки з великої таблиці бази даних, використовуйте інструкцію TRUNCATE.
- Для вилучення окремої підмножини рядків на основі певних критеріїв, використовуйте інструкцію DELETE.
Підводячи підсумки
Узагальнимо наш аналіз:
- Під час роботи з таблицями баз даних ви можете видалити як підмножину рядків, так і всі рядки таблиці. Для цього використовуються оператори TRUNCATE або DELETE.
- Оператор TRUNCATE має синтаксис: TRUNCATE TABLE ім’я_таблиці;. Він вилучає всі рядки в таблиці, зазначеній table_name, але не видаляє саму таблицю.
- Оператор DELETE має синтаксис: DELETE FROM table_name WHERE умова;. Він вилучає рядки, для яких умова-предикат є істинною.
- Виконання оператора SQL DELETE без умови WHERE вилучає всі рядки в таблиці. Таким чином, він функціонально виконує ту ж дію, що й оператор SQL TRUNCATE.
- Виконання TRUNCATE відбувається швидше при роботі з великими таблицями, оскільки він не сканує всю таблицю. Отже, коли потрібно видалити всі рядки у великій таблиці, використання урізання є більш ефективним.
- Якщо потрібно видалити підмножину рядків (на основі певної умови), необхідно скористатися оператором SQL DELETE.
Для швидкого ознайомлення з часто використовуваними командами SQL, перегляньте цю шпаргалку SQL.