Тестировщик хочет очистить промежуточную таблицу перед новым запуском теста. Должно ли это быть DELETE, TRUNCATE или DROP? Неправильный выбор может означать медленную работу, отсутствие стола или, в производственном процессе, очень плохой день.

Мы выполнили приведенные ниже примеры DELETE и DROP в SQLite 3.50, и результаты получены в результате этого запуска. В SQLite нет команды TRUNCATE, поэтому примечания TRUNCATE основаны на официальной документации PostgreSQL, MySQL и SQL Server, а не на нашем собственном тесте.

Короткий ответ

• Хотите удалить некоторые строки? Используйте DELETE ... WHERE.
• Нужно освободить стол, но сохранить его? Используйте TRUNCATE, где это поддерживается.
• Хотите, чтобы стол исчез навсегда? Используйте DROP TABLE.

УДАЛИТЬ: удалить строки

DELETE — команда изменения данных (DML). Он удаляет строки по одной, запускает триггеры на уровне строк и может фильтроваться. Создадим небольшую таблицу с триггером аудита:

CREATE TABLE logs (id INTEGER PRIMARY KEY AUTOINCREMENT, level TEXT);
INSERT INTO logs (level) VALUES ('INFO'),('ERROR'),('INFO'),('DEBUG');

CREATE TABLE audit (msg TEXT);
CREATE TRIGGER logs_delete AFTER DELETE ON logs
BEGIN INSERT INTO audit VALUES ('deleted ' || OLD.id); END;

DELETE FROM logs WHERE level = 'DEBUG';

SELECT id, level FROM logs;  -- (1,'INFO'), (2,'ERROR'), (3,'INFO')
SELECT * FROM audit;         -- 'deleted 4'

Отправляется только соответствующая строка, и триггер ее записывает. При выполнении DELETE FROM logs без предложения WHERE были удалены три другие строки, а таблица аудита содержала четыре записи, по одной на каждую удаленную строку.

DELETE не сбрасывает счетчики автоприращения. После очистки таблицы наша следующая вставка получила id 5, а не 1. В SQLite это из-за AUTOINCREMENT. Столбцы идентификаторов MySQL InnoDB и SQL Server также продолжают работу с того места, где они были после DELETE.

TRUNCATE: очистить таблицу

TRUNCATE TABLE logs; удаляет каждую строку сразу. Он не может использовать предложение WHERE. Вместо удаления строк одну за другой база данных обычно освобождает страницы данных таблицы, поэтому в больших таблицах это происходит намного быстрее.

Когда мы запустили его в SQLite, мы получили синтаксическую ошибку, поскольку в SQLite нет команды:

TRUNCATE TABLE logs;
-- near "TRUNCATE": syntax error

В SQLite используйте DELETE FROM logs; без предложения WHERE. В документации SQLite описана «оптимизация усечения», которая ускоряет работу, когда в таблице нет триггеров. В нашей таблице был триггер, поэтому удаление выполнялось построчно и запускалось для каждой строки.

В базах данных, поддерживающих TRUNCATE, согласно их документации:

• Обычно он не запускает триггеры DELETE на уровне строки.
• Обычно он сбрасывает счетчики идентификаторов или автоматического приращения, например, в MySQL и SQL Server. В PostgreSQL вы добавляете RESTART IDENTITY.
• В большинстве систем для этого требуются более строгие разрешения, чем для DELETE.

DROP: удалить саму таблицу

DROP TABLE — это команда схемы (DDL). Он удаляет данные, определение таблицы, ее индексы, триггеры и ограничения. Это также затрагивает все, что ссылается на таблицу, например представления или внешние ключи.

DROP TABLE logs;
SELECT * FROM logs;
-- no such table: logs

DROP TABLE IF EXISTS logs;  -- no error, even though it is gone

IF EXISTS удобен в сценариях установки, которые могут запускаться более одного раза. Представления удаленной таблицы могут сломаться, как мы показали в разделе Описание представлений SQL.

Сравнительная таблица

Можете ли вы откатиться назад?

Именно здесь многие статьи упрощают. Честный ответ: это зависит от базы данных.

DELETE можно откатить внутри транзакции везде. Мы протестировали это:

BEGIN;
DELETE FROM logs;
SELECT COUNT(*) FROM logs;  -- 0
ROLLBACK;
SELECT COUNT(*) FROM logs;  -- 3

DROP также откатился в SQLite, который поддерживает транзакционный DDL:

BEGIN;
DROP TABLE logs;
ROLLBACK;
SELECT COUNT(*) FROM logs;  -- 1 (the table is back)

Для TRUNCATE и DROP в других базах данных согласно их документации:

Таким образом, «TRUNCATE никогда не может быть откачен» неверно для PostgreSQL и SQL Server, а «его всегда можно откатить» неверно для MySQL. Вне транзакции ни одну из них нельзя отменить без резервной копии.

Почему TRUNCATE — это быстро

DELETE регистрирует каждую удаленную строку, чтобы изменение можно было отменить и реплицировать, запускает триггеры и проверяет ограничения построчно. В таблице с миллионами строк это требует много работы и объема журнала. TRUNCATE обычно освобождает память за одну операцию, записывая в журнал гораздо меньше. Чтобы удалить большое подмножество, а не все, удаляйте пакетно, чтобы избежать длительных блокировок. Мы рассматриваем стоимость блокировок и запросов в разделе Оптимизация SQL-запросов.

Внешние ключи и разрешения

• Внешние ключи. SQL Server отказывается выполнять TRUNCATE таблицу, на которую ссылается внешний ключ. PostgreSQL требует, чтобы вы также усекли ссылающиеся таблицы или использовали CASCADE. Вместо этого команда DELETE проверяет каждую строку на соответствие ограничениям.
• Разрешения. Для DELETE необходимы права на удаление. TRUNCATE часто требует ALTER или выделенного права TRUNCATE, а DROP требует владения или ALTER на схеме.
• Репликация и аудит. Если другие системы зависят от журналов изменений или триггеров на уровне строк, TRUNCATE может их пропустить. Сначала обратитесь к тому, кто владеет этими системами.

Какой из них использовать

Для тестовых данных TRUNCATE популярен, поскольку он сбрасывает идентификаторы, что делает тесты повторяемыми. Обертывание каждого теста в откатываемую транзакцию — это еще один вариант, описанный в разделе Транзакции SQL и ACID.

Контрольный список безопасности

1. Подтвердите, к какой базе данных и среде вы подключены.
2. Сначала запустите предложение WHERE как SELECT.
3. Оберните изменение в транзакцию, если ваша база данных его поддерживает.
4. Перед запуском TRUNCATE или DROP создайте недавнюю проверенную резервную копию.
5. Добавляйте операторы DROP в проверенные сценарии миграции, а не в специальные консоли.
6. Прежде чем удалять таблицу, проверьте, что зависит от таблицы, например представления, внешние ключи и отчеты.

Безопасное удаление больших объемов данных

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

-- SQL Server style (not run for this article)
DELETE TOP (5000) FROM logs WHERE created_at < '2026-01-01';

-- PostgreSQL style (not run for this article)
DELETE FROM logs
WHERE id IN (SELECT id FROM logs WHERE created_at < '2026-01-01' LIMIT 5000);

Выполняйте оператор в цикле с небольшой паузой между пакетами, пока он не сообщит о нулевых строках. Каждый пакет представляет собой небольшую транзакцию, поэтому блокировки короткие, а другие запросы продолжают выполняться. Индекс в столбце фильтра, здесь created_at, обеспечивает быстроту каждой партии.

Для данных, которые растут по дате, таких как журналы и события, разделение таблиц еще лучше. Каждый месяц живет в своем собственном разделе, и удаление старых данных становится быстрой операцией, которая удаляет или усекает один раздел вместо удаления строк одну за другой.

Мягкое удаление

Многие приложения никогда физически не удаляют деловые записи. Они добавляют столбец, например deleted_at, и устанавливают его вместо него, а затем запросы фильтруются по deleted_at IS NULL. Обратное удаление упрощает восстановление и сохраняет историю для аудита, но каждый запрос должен помнить фильтр, что хорошо для представления. Ограничения уникальности также требуют внимания, поскольку обратимо удаленная строка по-прежнему сохраняет свое значение. Законы о конфиденциальности могут требовать фактического удаления личных данных по истечении определенного периода, поэтому обратимое удаление не заменяет политику хранения.

Итоги

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

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

Продолжайте учиться