DELETE в SQL: когда одно слово может стереть ваши данные⁠⁠

DELETE — инструменте, который может как спасти вашу базу данных от хлама, так и превратить её в пустыню за считанные секунды.

DELETE как цифровой пылесос**, который может убрать всё, что вы ему скажете. Только вот вернуть обратно будет ой как непросто!

А пока подписывайся на мой канал На связи: SQL Там я публикую посты про особенности и нюансы SQL. Этот канал про то, как не бояться баз данных, понимать, что такое JOIN, GROUP BY и почему NULL ≠ 0. Его я веду с нуля подписчиков. Присоединяйся!

Базовый синтаксис:

DELETE FROM таблица
WHERE условие;

Можно использовать EXISTS с подзапросами

DELETE FROM orders

WHERE EXISTS (

SELECT 1

FROM customers

WHERE customers.id = orders.customer_id

AND customers.status = 'deleted' );

Удаляет заказы неактивных клиентов.

Можно использовать JOIN

DELETE o

FROM orders o

INNER JOIN customers c ON o.customer_id = c.id

WHERE c.registration_date < '2020-01-01';

Удаляет заказы клиентов, зарегистрированных до 2020 года.

Как происходит удаление записей?

Физическое удаление в SQL — это не мгновенное стирание данных с диска. Когда вы выполняете DELETE:

• Система записывает операцию в журнал транзакций

• Помечает удалённые строки как свободные

• Физическое место может быть использовано для новых данных

• Сами данные остаются в файле некоторое время

Формируется основной файл .mdf (или аналогичный). Также информация записывается в журнал транзакций. Сроки хранения удаленных данных зависят от:
- модели восстановления базы данных
- активности базы данных
- настроек резервного копирования
- объема свободного места

Удаленные данные могут храниться до следующего бэкапа или до перезаписи журнала.

Способы восстановления данных после DELETE

1. Восстановление из резервной копии

   RESTORE DATABASE имя_базы FROM DISK = 'путь_к_бэкапу'

2. Использование моментальных снимков (Snapshot)

   CREATE DATABASE имя_снимка

   ON (NAME = имя_файла, FILENAME = 'путь_к_файлу')

   AS SNAPSHOT OF исходная_база;

   Восстановление данных из снимка:

   INSERT INTO исходная_таблица

   SELECT * FROM снимок.dbo.таблица

3. Восстановление через LSN (Log Sequence Number)

   Если есть журналы транзакций:

   BACKUP LOG имя_базы TO DISK = 'путь_к_журналу'

   Поиск удалённых записей:
   SELECT [Current LSN], [Transaction ID]

   FROM fn_dblog(NULL, NULL)

   WHERE Operation = 'LOP_DELETE_ROWS'

4. Либо использовать специальные посторонние программы для восстановления БД

Чтобы предотвратить потерю данных:

• Регулярно создавайте резервные копии

• Используйте транзакции (BEGIN TRANSACTION / ROLLBACK)

• Тестируйте DELETE-запросы на тестовой базе

• Применяйте WHERE с осторожностью

• Настройте политики резервного копирования

DELETE vs TRUNCATE

Многие думают, что TRUNCATE и DELETE — это одно и то же. На самом деле:

• DELETE удаляет строки по одной и записывает каждую операцию в журнал транзакций

• TRUNCATE мгновенно очищает таблицу, минуя журнал (кроме некоторых случаев)

При выполнении DELETE:

• Блокируются только удаляемые строки

• Другие транзакции могут читать незаблокированные данные

• В некоторых СУБД возможна эскалация блокировок до уровня таблицы

• Не затрагивает структуру индексов

• Индексы остаются в прежнем состоянии

• Это ускоряет процесс удаления

• Не сбрасывает счётчики автоинкремента

• Сохраняет текущее значение последовательности

• Важно при работе с первичными ключами

• Активируются триггеры AFTER DELETE

• Можно отслеживать удалённые строки через виртуальную таблицу deleted

• Триггеры могут отменить операцию удаления

Чтобы сохранить целостность данных можно использовать ON DELETE CASCADE
Это позволит там, где есть зависимость по внешним ключам произвести удаление зависимых строк.