В чём разница между DELETE и TRANCATE?

Разница между DELETE и TRUNCATE

DELETE и TRUNCATE — это две команды для удаления данных из таблицы, но они работают совершенно по-разному на уровне внутреннего механизма базы данных.

DELETE

DELETE — это DML операция (Data Manipulation Language), которая удаляет строки построчно. Она может работать с условиями WHERE и откатываться в транзакции.

DELETE FROM users WHERE age > 65;
DELETE FROM orders WHERE created_at < "2020-01-01";

Основные характеристики DELETE:

• Можно использовать WHERE условие для выбора конкретных строк
• Генерирует логи для каждой удаленной строки (медленнее)
• Откатывается в транзакции (ROLLBACK работает)
• Сохраняет identity seed (автоинкремент продолжает считать)
• Удаляет данные, но не освобождает память (место на диске остаётся)
• Можно восстановить данные из логов транзакции
• Срабатывают триггеры на удаление

TRUNCATE

TRUNCATE — это DDL операция (Data Definition Language), которая очищает таблицу, удаляя все строки сразу и освобождая место на диске.

TRUNCATE TABLE users;
TRUNCATE TABLE orders;

Основные характеристики TRUNCATE:

• Без WHERE условия — удаляет всегда все строки
• Не генерирует логи для каждой строки (очень быстрая операция)
• Может откатываться в транзакции (в большинстве СУБД)
• Сбрасывает identity seed — автоинкремент начинает считать заново
• Освобождает дисковое пространство, выделенное под таблицу
• Восстановление данных затруднено
• Триггеры НЕ срабатывают
• Требует прав больше чем DELETE

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

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

-- DELETE с условием
DELETE FROM employees WHERE department_id = 5;

-- TRUNCATE всей таблицы
TRUNCATE TABLE temp_logs;

-- DELETE с ограничением по количеству
DELETE FROM audit_log WHERE id IN (
  SELECT id FROM audit_log ORDER BY created_at LIMIT 1000
);

Рекомендации

• Используй TRUNCATE когда нужно очистить таблицу полностью и быстро (логи, временные таблицы)
• Используй DELETE когда нужно удалить конкретные строки по условию
• Для больших таблиц с условием можно использовать DELETE с ограничением батчами, чтобы не заблокировать БД
• При работе с identity нужно помнить про TRUNCATE, которое его сбросит