В SQL есть три команды, которые удаляют данные, — DELETE, TRUNCATE и DROP. Разберем, как работает каждая из них и в каких случаях их следует использовать.
DELETE, TRUNCATE и DROP: как они работают и чем отличаются
На первый взгляд эти команды похожи, но на практике они выполняют разные задачи:
- DELETE удаляет одну, несколько или все строки из таблицы;
- TRUNCATE очищает таблицу целиком, сохраняя ее структуру;
- DROP удаляет саму таблицу вместе с данными и всеми связанными объектами (индексами, ограничениями и триггерами).
Разница между DELETE, TRUNCATE и DROP
| Критерий | DELETE | TRUNCATE | DROP |
| Что удаляет | Конкретные строки или все записи | Все записи (очистка) | Таблицу целиком как объект |
| Тип команды | DML (Data Manipulation Language) | DDL (Data Definition Language) | DDL (Data Definition Language) |
| Условие WHERE | Поддерживается | Не поддерживается | Не поддерживается |
| Скорость работы | Медленно (построчно) | Быстро (освобождает блоки) | Мгновенно |
| Сброс счетчиков | Нет | Да (зависит от СУБД) | Удаляет вместе со счетчиком |
DELETE для удаления строк
DELETE используют, когда нужно удалить одну или несколько строк из таблицы. Команда работает с данными, но не удаляет саму таблицу, ее столбцы, индексы и ограничения.
Синтаксис
DELETE FROM имя_таблицы WHERE условие;
DELETE с WHERE
Обычно DELETE используют с оператором WHERE. Это условие указывает на строки, которые нужно удалить. Например, можно удалить пользователя по id:
DELETE FROM users WHERE id = 10;
Такой запрос удалит только ту строку, у которой id = 10.
Команда отработает корректно, если условие точное и заранее проверено через SELECT:
SELECT * FROM users WHERE id = 10;
DELETE с WHERE также подходит для удаления нескольких строк. Например, можно удалить всех неактивных пользователей (со статусом inactive):
DELETE FROM users WHERE status = 'inactive';
Как и в случае с одной строкой, лучше проверить условие через SELECT, чтобы случайно не удалить лишние строки.
Кроме того, удаление может завершиться ошибкой, если вы удаляете строку из родительской таблицы, а на нее ссылаются строки из дочерней. Это происходит, если для внешнего ключа не настроено действие, которое разрешает такое удаление (например, каскадное удаление — ON DELETE CASCADE).
Синтаксически удаление и одной, и нескольких конкретных строк у DELETE выглядит одинаково. Но мы можем заранее понять, что если мы удаляем строки по id, то обычно речь идет об одной записи, потому что id часто делают первичным ключом (PRIMARY KEY) или уникальным столбцом (UNIQUE). А вот записей с неуникальными критериями может быть несколько.
DELETE без WHERE
Если использовать DELETE без условия WHERE, то запрос очистит всю таблицу от записей. При этом структура, столбцы и ограничения останутся. Например, очистка таблицы users от записей выглядит так:
DELETE FROM users;
Очистка таблицы с TRUNCATE
TRUNCATE используют, когда нужно удалить все строки из таблицы, но сохранить столбцы, индексы, ограничения и другие элементы структуры.
Синтаксис
TRUNCATE TABLE имя_таблицы;
Например, очистим таблицу users.
TRUNCATE TABLE users;
Эта команда так же, как и DELETE, очищает таблицу, но принцип работы у них разный. DELETE может удалить все строки из таблицы, но чаще его используют для условного удаления записей с помощью WHERE. TRUNCATE предназначен именно для полной очистки таблицы, и на больших таблицах он обычно быстрее, чем DELETE без WHERE.
Кроме того, в команде с TRUNCATE нельзя указать WHERE, так как она всегда очищает таблицу целиком.
TRUNCATE в PostgreSQL
В разных СУБД TRUNCATE ведет себя по-разному.
В PostgreSQL TRUNCATE очищает таблицу так же, как DELETE без WHERE. При этом он работает быстрее на больших таблицах, потому что не сканирует таблицу построчно и сразу освобождает место, занятое данными таблицы.
В PostgreSQL для автоувеличения значений часто используют последовательности. По умолчанию TRUNCATE не сбрасывает их значения. Например, счетчик id не обновится, а автонумерация продолжится с текущего счетчика. Если нужно очистить таблицу и начать счетчик заново, добавляют опцию RESTART IDENTITY:
TRUNCATE TABLE users RESTART IDENTITY;
Если же таблица является родительской, то есть на нее ссылаются другие таблицы, PostgreSQL не даст выполнить обычный TRUNCATE, пока эти дочерние таблицы не участвуют в той же команде.
Например, если таблица orders ссылается на users, такой запрос может завершиться ошибкой:
TRUNCATE TABLE users;
Чтобы этого не произошло, можно явно очистить связанные таблицы вместе:
TRUNCATE TABLE orders, users;
А ещё можно использовать ключевое слово CASCADE:
TRUNCATE TABLE users CASCADE;
CASCADE нужно использовать осторожно, так как в связке с TRUNCATE он очищает зависимые таблицы целиком.
TRUNCATE в MySQL
В MySQL операция TRUNCATE TABLE очищает данные, но делает это не как построчный DELETE, а скорее удаляет и пересоздает пустую таблицу. Поэтому TRUNCATE:
- требует привилегии DROP, которую устанавливает администратор базы данных (пользователю недостаточно иметь право удалять строки из таблицы, нужны права на операцию уровня структуры таблицы, потому что MySQL обрабатывает TRUNCATE как DDL-команду);
- не запускает триггеры ON DELETE — триггеры, которые должны срабатывать при удалении строк через DELETE, не выполнятся (например, если в базе есть триггер, который при удалении пользователя записывает событие в таблицу аудита, то при TRUNCATE TABLE users такая запись не появится);
- сбрасывает AUTO_INCREMENT — счетчик, который автоматически выдает новые значения для новых записей, сбрасывается ( если в PostgreSQL счетчик сбрасывают через дополнительную опцию RESTART IDENTITY, то в MySQL это происходит сразу после TRUNCATE);
- может не выполниться для InnoDB-таблицы, если на нее ссылаются внешние ключи из других таблиц — так как TRUNCATE обходит DML-механизм удаления данных, к нему не применяется логика построчного DELETE, включая каскадное удаление строк через внешний ключ, а если другая таблица ссылается на очищаемую InnoDB-таблицу, то MySQL не даст выполнить TRUNCATE, чтобы не нарушить связи между таблицами.
TRUNCATE в SQL Server
В SQL Server вместо удаления каждой строки TRUNCATE TABLE освобождает страницы данных, которые хранят строки таблицы и индексы.
Страница данных — это внутренний блок хранения в SQL Server.
Таблица физически хранится как набор таких блоков. Если DELETE работает со строками, то TRUNCATE освобождает блоки, где эти строки лежали.
Как и в MySQL, в SQL Server нельзя выполнить TRUNCATE TABLE для таблицы, на которую ссылается внешний ключ из другой таблицы.
DROP для удаления таблицы целиком
DROP используют, когда таблицу нужно удалить полностью. В отличие от DELETE и TRUNCATE, эта команда убирает не только строки, но и саму таблицу как объект базы данных.
Синтаксис
DROP TABLE имя_таблицы;
Удаление таблицы users будет выглядеть так:
DROP TABLE users;
После такой команды таблица удалится, а SELECT-запрос на нее будет возвращать ошибку:
SELECT * FROM users;
Если нужно удалить таблицу только при том условии, что она существует, используют конструкцию IF EXISTS:
DROP TABLE IF EXISTS users;
Если таблицы нет, то такой запрос на удаление не вызовет ошибку.
DROP в PostgreSQL
В PostgreSQL команда DROP TABLE удаляет таблицу из базы данных. Вместе с таблицей всегда удаляются индексы, правила, триггеры и ограничения, которые принадлежат этой таблице.
Но если на таблицу ссылается представление (View) или внешний ключ из другой таблицы, PostgreSQL не даст удалить таблицу обычной командой. Для этого нужно явно указать CASCADE:
DROP TABLE users CASCADE;
CASCADE автоматически удаляет зависимые объекты. Если зависимость связана с внешним ключом, PostgreSQL удалит само ограничение внешнего ключа, а не другую таблицу целиком. Если это зависимое представление, то оно будет полностью удалено.
Всегда проверяйте, какие зависимости есть у таблицы, чтобы не удалить что-то случайно.
DROP в MySQL
В MySQL DROP TABLE удаляет определение таблицы и все данные в ней. Если таблица была секционированной, удаляются и ее секции. Кроме того, при этом удаляются и связанные с таблицей триггеры. Для выполнения команды нужны права на осуществление DROP-операции (их выдает администратор базы данных).
DROP в SQL Server
В SQL Server DROP TABLE удаляет определение таблицы, данные, индексы, триггеры, ограничения и разрешения для этой таблицы. Но представления и хранимые процедуры, которые ссылаются на удаленную таблицу, нужно удалять отдельно через DROP VIEW или DROP PROCEDURE.
Кроме того, в SQL Server нельзя удалить таблицу через DROP TABLE, если на нее ссылается внешний ключ из другой таблицы. Сначала нужно удалить внешний ключ или таблицу, которая на нее ссылается, а потом уже нужную таблицу.
DELETE, TRUNCATE и DROP: коротко о главном
- DELETE, TRUNCATE и DROP связаны с удалением, но решают разные задачи.
- DELETE удаляет строки из таблицы. Его используют, когда нужно удалить одну запись, несколько записей или все записи, но сохранить саму таблицу. Если указать WHERE, удалятся только определенные строки. Если не указать WHERE, таблица очистится от всех записей.
- TRUNCATE очищает таблицу целиком и сохраняет ее структуру. Его используют, когда нужно быстро удалить все строки, но оставить таблицу, столбцы, индексы и ограничения. У TRUNCATE нельзя указать WHERE для выборочного удаления.
- DROP удаляет таблицу полностью. После DROP TABLE исчезают не только данные, но и сама таблица как объект базы данных. Чтобы работать с таблицей, ее придется создавать заново.
