Сравнение Truncate и Delete в SQL

В SQL команды TRUNCATE и DELETE используются для удаления данных из таблицы, но у них есть несколько ключевых отличий:

1. Уровень работы с данными:

• DELETE: Удаляет строки по одной. Каждая строка удаляется по отдельности, с возможностью использования условий (через WHERE).
• TRUNCATE: Удаляет все строки сразу, работает на уровне всей таблицы. Нельзя использовать условие WHERE.

2. Логирование:

• DELETE: Операция логируется для каждой удалённой строки (в журналах транзакций хранится запись об удалении каждой строки). Это позволяет откатить транзакцию (rollback).
• TRUNCATE: Логируются только факты удаления страниц данных, а не всех строк по отдельности, что делает операцию быстрее.

3. Скорость:

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

4. Влияние на идентификаторы (AUTO_INCREMENT):

• DELETE: При использовании автоинкрементных полей (например, AUTO_INCREMENT в MySQL), удаление строк не сбрасывает счётчик автоинкремента.
• TRUNCATE: Сбрасывает счётчик автоинкремента, начиная его заново с установленного значения (обычно с 1).

5. Триггеры:

• DELETE: Запускает триггеры, связанные с удалением строк (например, AFTER DELETE).
• TRUNCATE: Триггеры не срабатывают, так как строки не удаляются по одной.

6. Операция транзакции:

• DELETE: Можно использовать в рамках транзакции, и при необходимости откатить (сделать rollback).
• TRUNCATE: В большинстве СУБД также можно использовать в транзакции, но есть исключения в некоторых системах или версиях.

7. Ограничения (Foreign Keys):

• DELETE: Удаление возможно даже в таблицах, которые ссылаются на другие таблицы через внешние ключи (при этом могут удаляться связанные строки в других таблицах, если настроены каскадные удаления).
• TRUNCATE: Часто не позволяет удалять данные в таблицах, которые участвуют во внешних ключах (в зависимости от СУБД).

8. Синтаксис:

• DELETE:

Условие WHERE может быть опущено для удаления всех строк:

• TRUNCATE:

9. Тип команды (DDL vs DML):

• DELETE: Является командой DML (Data Manipulation Language) — языка манипуляции данными. Это позволяет изменять содержимое таблицы (вставка, обновление, удаление данных), но не саму структуру. Команда DELETE манипулирует данными в таблице, удаляя строки, но не затрагивает структуру таблицы.
• TRUNCATE: Является командой DDL (Data Definition Language) — языка определения данных. Этот тип команд изменяет структуру объектов базы данных (таблиц, индексов и т.д.), и такие команды, как правило, автоматически фиксируются (commit) в момент выполнения. Например, при выполнении TRUNCATE система воспринимает это как изменение структуры таблицы (удаление всех данных).

Когда что использовать:

• DELETE лучше использовать, когда нужно удалить только часть данных или когда нужно запускать триггеры.
• TRUNCATE предпочтительнее для быстрого удаления всех данных из таблицы, когда не требуется логировать каждую удалённую строку.

Важно помнить, что после выполнения TRUNCATE или DELETE данные будут потеряны, если операция не находится в транзакции и не будет отменена.

Если Вам интересно, что еще можно найти на канале QA Helper, прочитайте статью: Вместо оглавления. Что вы найдете на канале QA Helper - справочник тестировщика?

Не забудьте подписаться на канал, чтобы не пропустить полезную информацию: QA Helper - справочник тестировщика

Пишите в комментариях какой пункт было бы интересно рассмотреть более подробно.

Обязательно прочитайте: Что должен знать и уметь тестировщик

Также будет интересно почитать: Вопросы которые задают на собеседовании тестировщикам