DML. DELETE и TRUNCATE

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

В отличии от вставки и обновления записей, для удаления предусмотрены сразу два оператора, имеющие ряд собственных особенностей. Поэтому рассмотрим (в меру текущих знаний) оба и постараемся разобраться, когда стоит использовать один, а когда – другой.

DELETE

Оператор DELETE позволяет удалять из таблицы все записи:

delete from passenger;

Или по определенному условию:

 delete from passenger where male;

Особенность данного оператора заключается в том, что он удаляет записи построчно, вне зависимости от наличия или содержимого блока WHERE. Это позволяет вносить данные об удаленных строках во внутренний журнал событий СУБД и несет ряд других бонусов (по мере знакомства с определенными темами я постараюсь подсвечивать моменты, которые не расписаны сейчас).

Ценой же становится производительность – удаление, особенно больших массивов данных, может оказаться слишком дорогим по времени. А также определенные особенности в организации памяти, из-за которых удаление через DELETE может привести к дальнейшему не эффективному ее использованию.

Хорошей новостью является то, что озвученные проблемы редко становятся головной болью разработчика.

И, наконец, DELETE имеет еще один не очевидный, но очень важный плюс. В отличии от TRUNCATE, который мы рассмотрим в следующем пункте, принцип и нюансы работы DELETE мало отличаются от СУБД к СУБД. Таким образом, в большинстве СУБД DELETE имеет примерно одинаковые особенности и недостатки, что делает его достаточно предсказуемым инструментом.

TRUNCATE же, в свою очередь, в зависимости от СУБД может иметь различные особенности, которые несут то или иное число сторонних эффектов, порой неожиданных. Плюсом является то, что в рассматриваемом нами PostgreSQL большинство из этих эффектов не проявляются или минимизированы.

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

TRUNCATE

Оператор TRUNCATE, в общем случае, имеет максимально простой синтаксис:

truncate table passenger;

Данный запрос удалит все записи из таблицы passenger. Причем, в отличии от DELETE, удаление НЕ будет построчным – удаление через TRUNCATE является единой атомарной операцией.

При этом TRUNCATE не позволяет вводить какие-то условия удаления или что-то еще – он всегда очищает всю таблицу.

PostgreSQL имеет некоторые кастомизации для TRUNCATE в определенных условиях. Но подсвечу эти моменты подробнее в соответствующих уроках. Это связано с удалением данных из связанных таблиц, если в соответствующих уроках не увидите пункта про TRUNCATE – пинайте:)

Из-за особенностей реализации, TRUNCATE не указывает удаленные строки в журнале событий, а для удаленных строк не срабатывают триггеры*, если они были.

Триггер – функция, которая срабатывает автоматически, при выполнении определенных условий. Например, добавление, обновление или удаление записи в таблице.

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

В целом, короткие блоки сравнения DELETE и TRUNCATE будут появляться в дальнейших статьях: транзакции, PRIMARY/FOREIGN KEY и т.д. Все же, основная задача данного урока – знакомство с синтаксисом. Полноценный сравнительный анализ требует большего багажа знаний, чем дано в рамках предыдущих уроков.

Наконец, важно подсветить, что нюансы реализации TRUNCATE разнятся от СУБД к СУБД, что дает различные эффекты и ограничения при использовании. Их, опять же, будем подсвечивать по мере возможности.

В качестве примера, в ряде СУБД (НЕ в PostgreSQL) TRUNCATE сбрасывает значение автоинкремента (как в bigserial). Таким образом, при добавлении в таблицу новых записей после вызова TRUNCATE, нумерация начинается с базового значения (обычно – 1). DELETE такого эффекта не имеет – нет гарантий, что он полностью очистил таблицу.

Но, как уже сказано, это не относится к PostgreSQL. В нем тоже можно добиться такого поведения, но это потребует использование дополнительного модификатора для TRUNCATE. Не вижу смысла вдаваться в подробности, но тему и примеры легко нагуглить по запросу «postgres truncate restart identity».

Заключение

Тема удаления данных из таблицы не слишком обширна. Но имеет свои подводные камни, не все из которых можно легко разобрать на текущем этапе или вообще в формате курса.

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

С теорией на сегодня все!

Смысла в отдельной практике не вижу, кроме самих операторов ничего нового в данном уроке нет.

Если что-то непонятно или не получается – welcome в комменты к посту или в лс:)

Канал: https://t.me/ViamSupervadetVadens

Мой тг: https://t.me/ironicMotherfucker

Дорогу осилит идущий!