SQL TRUNCATE и DROP TABLE

Урок 9.3 · Время чтения: ~6 мин · Задачи для практики ↓

TRUNCATE в SQL удаляет все строки таблицы, но оставляет саму таблицу; DROP TABLE удаляет таблицу целиком - вместе с данными и структурой. В этом уроке вы узнаете, как работают оба оператора, чем они отличаются от DELETE по скорости, возможности отката и поведению автоинкремента и как на них влияют внешние ключи.

Удалять данные можно на трёх уровнях: часть строк (DELETE ... WHERE), все строки (TRUNCATE) или всю таблицу (DROP TABLE). Первый вариант разобран в уроке 8.3. TRUNCATE и DROP TABLE - операторы DDL: они работают с таблицей целиком и выполняются гораздо быстрее, но и ошибок не прощают.

Учебные базы данных на SQLtest доступны только для чтения; примеры создают собственную таблицу, поэтому их можно запускать в песочнице на базе без учебных данных.

TRUNCATE: очистка таблицы

TRUNCATE TABLE table_name;

После TRUNCATE:

• все строки удалены;
• таблица, её столбцы, индексы и ограничения остаются;
• счётчик автоинкремента снова начинается с 1.

CREATE TABLE daily_import (
    id INT AUTO_INCREMENT PRIMARY KEY,
    payload VARCHAR(100)
);
INSERT INTO daily_import (payload) VALUES ('a'), ('b'), ('c');

TRUNCATE TABLE daily_import;

INSERT INTO daily_import (payload) VALUES ('d');
SELECT * FROM daily_import;

Результат: одна строка, id = 1, payload = 'd'. Таблица очищена, а её счётчик id сброшен.

DROP TABLE: удаление таблицы

DROP TABLE table_name;

После DROP TABLE таблицы больше не существует: её данные, столбцы, индексы и ограничения удалены, а любой запрос к ней завершится ошибкой, пока таблицу не создадут заново.

DROP TABLE IF EXISTS old_reports;

IF EXISTS пропускает оператор без ошибки, если таблицы нет, - это удобно в скриптах, которые пересоздают таблицы.

TRUNCATE, DELETE и DROP

Важнее всего строка про откат. В MySQL и MariaDB операторы DDL автоматически фиксируют текущую транзакцию, поэтому ошибочный TRUNCATE или DROP TABLE нельзя отменить с помощью ROLLBACK. Поможет только резервная копия.

Внешние ключи

Внешние ключи защищают таблицы, на которые ссылаются другие таблицы:

• DROP TABLE завершается ошибкой, если у другой таблицы есть внешний ключ на эту. Сначала удалите дочернюю таблицу или само ограничение.
• В MySQL TRUNCATE завершается ошибкой для таблицы, на которую ссылается внешний ключ, даже если дочерняя таблица пуста. DELETE FROM в этом случае работает, потому что проверяет реальные строки.

CREATE TABLE departments (department_id INT PRIMARY KEY);
CREATE TABLE staff (
    staff_id INT PRIMARY KEY,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments (department_id)
);

-- Ошибка: Cannot truncate a table referenced in a foreign key constraint
TRUNCATE TABLE departments;

Что выбрать

• Удалить часть строк → DELETE ... WHERE.
• Очистить таблицу, которую вы заполните снова, например промежуточную таблицу для импорта → TRUNCATE.
• Таблица больше не нужна → DROP TABLE.

Перед TRUNCATE или DROP на реальной базе проверьте имя таблицы и базу, к которой вы подключены: оба оператора срабатывают мгновенно и в MySQL - без возможности вернуть всё назад.

Ключевые выводы:

• TRUNCATE TABLE быстро удаляет все строки и оставляет таблицу; счётчик автоинкремента сбрасывается.
• DROP TABLE удаляет таблицу вместе с данными и структурой; IF EXISTS избавляет от ошибки.
• DELETE может удалить часть строк и может быть отменён; TRUNCATE не поддерживает WHERE.
• В MySQL и MariaDB TRUNCATE и DROP выполняют неявный COMMIT и не откатываются.
• Внешние ключи не дают удалить или очистить таблицу, на которую ссылаются.

Часто задаваемые вопросы

Чем TRUNCATE отличается от DELETE?

DELETE удаляет строки по одной, может использовать WHERE, вызывает триггеры удаления и может быть отменён. TRUNCATE удаляет все строки сразу, работает гораздо быстрее, сбрасывает счётчик автоинкремента и в MySQL не откатывается.

Чем TRUNCATE отличается от DROP TABLE?

TRUNCATE очищает таблицу, но оставляет её готовой к новым строкам. DROP TABLE удаляет таблицу полностью; чтобы снова её использовать, нужно создать её с помощью CREATE TABLE.

Можно ли откатить TRUNCATE?

В MySQL и MariaDB - нет: он выполняет неявный COMMIT. В PostgreSQL и SQL Server - да, если он выполняется внутри транзакции.