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

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

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

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

А пока подписывайся на мой канал На связи: 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 (или аналогичный). Также информация записывается в журнал транзакций. Сроки хранения удаленных данных зависят от:- модели восстановления базы данных- активности базы данных- настроек резервного копирования- объема свободного места

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

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

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

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

-- Создание Snapshot CREATE DATABASE имя_снимка ON (NAME = имя_файла, FILENAME = 'путь_к_файлу') AS SNAPSHOT OF исходная_база; -- Восстановление данных из Snapshot 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 с осторожностью
Настройте политики резервного копирования

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

DELETE удаляет строки по одной и записывает каждую операцию в журнал транзакций
TRUNCATE мгновенно очищает таблицу, минуя журнал (кроме некоторых случаев)

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

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

Не затрагивает структуру индексов
Индексы остаются в прежнем состоянии
Это ускоряет процесс удаления

Не сбрасывает счётчики автоинкремента
Сохраняет текущее значение последовательности
Важно при работе с первичными ключами

Активируются триггеры AFTER DELETE
Можно отслеживать удалённые строки через виртуальную таблицу deleted
Триггеры могут отменить операцию удаления

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

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

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

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

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

DELETE vs TRUNCATE