DELETE — это оператор DML, удаляющий строки из таблицы.

Оператор DELETE может начинаться с предложения WITH для определения общих табличных выражений, доступных в рамках оператора DELETE. См. Раздел 15.2.20, «WITH (Общие табличные выражения)».

Синтаксис для одной таблицы

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias]
    [PARTITION (partition_name [, partition_name] ...)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

Оператор DELETE удаляет строки из tbl_name и возвращает количество удалённых строк. Чтобы проверить количество удалённых строк, вызовите функцию ROW_COUNT(), описанную в Разделе 14.15, «Функции информации».

Основные предложения

Условия в необязательном предложении WHERE определяют, какие строки подлежат удалению. При отсутствии предложения WHERE удаляются все строки.

where_condition — это выражение, которое вычисляется как истинное для каждой строки, подлежащей удалению. Оно задаётся так, как описано в Разделе 15.2.13, «Оператор SELECT».

Если указано предложение ORDER BY, строки удаляются в указанном порядке. Предложение LIMIT устанавливает ограничение на количество удаляемых строк. Эти предложения применяются к операциям удаления из одной таблицы, но не к операциям удаления из нескольких таблиц.

Синтаксис для нескольких таблиц

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] ...
    FROM table_references
    [WHERE where_condition]

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*]] ...
    USING table_references
    [WHERE where_condition]

Привилегии

Для удаления строк из таблицы необходимо иметь привилегию DELETE на этой таблице. Для столбцов, которые только считываются (например, тех, что указаны в предложении WHERE), достаточно привилегии SELECT.

Производительность

Если вам не нужно знать количество удалённых строк, оператор TRUNCATE TABLE — более быстрый способ опустошить таблицу, чем оператор DELETE без предложения WHERE. В отличие от оператора DELETE, оператор TRUNCATE TABLE нельзя использовать в транзакции или если таблица заблокирована. См. Раздел 15.1.37, «Оператор TRUNCATE TABLE» и Раздел 15.3.6, «Операторы LOCK TABLES и UNLOCK TABLES».

Скорость операций удаления также может зависеть от факторов, обсуждаемых в Разделе 10.2.5.3, «Оптимизация операторов DELETE».

Чтобы гарантировать, что оператор DELETE не займёт слишком много времени, MySQL-специфическое предложение LIMIT row_count для оператора DELETE задаёт максимальное количество строк для удаления. Если число строк для удаления больше предела, повторите оператор DELETE до тех пор, пока число затронутых строк не станет меньше значения LIMIT.

Подзапросы

Нельзя удалять из таблицы и выбирать из неё одновременно в подзапросе.

Поддержка таблиц с разбиением

DELETE поддерживает явное указание разделов с помощью предложения PARTITION, которое принимает список разделённых запятыми имён одного или нескольких разделов или субразделов (или обоих), из которых нужно выбирать строки для удаления. Разделы, не включённые в список, игнорируются. Учитывая таблицу с разбиением t с разделом, названным p0, выполнение оператора DELETE FROM t PARTITION (p0) имеет тот же эффект на таблицу, что и выполнение ALTER TABLE t TRUNCATE PARTITION (p0); в обоих случаях все строки в разделе p0 удаляются.

PARTITION можно использовать вместе с условием WHERE, в этом случае условие проверяется только на строках в указанных разделах. Например, DELETE FROM t PARTITION (p0) WHERE c < 5 удаляет строки только из раздела p0, для которых условие c < 5 истинно; строки в других разделах не проверяются и, следовательно, не затрагиваются оператором DELETE.

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

Более подробная информация и примеры приведены в Разделе 26.5, «Выбор разделов».

Столбцы с автоматическим увеличением

Если вы удаляете строку, содержащую максимальное значение для столбца AUTO_INCREMENT, значение не используется повторно для таблиц MyISAM или InnoDB. Если вы удаляете все строки в таблице с DELETE FROM tbl_name (без предложения WHERE) в режиме autocommit, последовательность начинается заново для всех движков хранения, кроме InnoDB и MyISAM. Есть некоторые исключения из этого поведения для таблиц InnoDB, как описано в Разделе 17.6.1.6, «Обработка AUTO_INCREMENT в InnoDB».

Для таблиц MyISAM можно указать дополнительный столбец AUTO_INCREMENT в составном ключе. В этом случае переиспользование значений, удаленных из начала последовательности, происходит даже для таблиц MyISAM. См. Раздел 5.6.9, «Использование AUTO_INCREMENT».

Модификаторы

Оператор DELETE поддерживает следующие модификаторы:

Порядок удаления

Если оператор DELETE включает предложение ORDER BY, строки удаляются в порядке, указанном в предложении. Это полезно в первую очередь в сочетании с LIMIT. Например, следующий оператор находит строки, соответствующие предложению WHERE, сортирует их по timestamp_column и удаляет первую (самую старую):

DELETE FROM somelog WHERE user = 'jcole'
ORDER BY timestamp_column LIMIT 1;

ORDER BY также помогает удалить строки в порядке, необходимом для предотвращения нарушений целостности ссылок.

Таблицы InnoDB

Если вы удаляете много строк из большой таблицы, вы можете превысить размер таблицы блокировки для таблицы InnoDB. Чтобы избежать этой проблемы, или просто чтобы свести к минимуму время, в течение которого таблица остаётся заблокированной, может быть полезна следующая стратегия (которая вообще не использует DELETE):

Никакие другие сессии не могут получить доступ к вовлечённым таблицам, пока выполняется оператор RENAME TABLE, поэтому операция переименования не подвержена проблемам конкурентного доступа. См. Раздел 15.1.36, «Оператор RENAME TABLE».

Таблицы MyISAM

В таблицах MyISAM удалённые строки сохраняются в связанном списке, и последующие операции INSERT повторно используют старые позиции строк. Чтобы освободить неиспользуемое пространство и уменьшить размер файлов, используйте оператор OPTIMIZE TABLE или утилиту myisamchk для реорганизации таблиц. OPTIMIZE TABLE проще в использовании, но myisamchk быстрее. Смотрите Раздел 15.7.3.4, «Оператор OPTIMIZE TABLE» и Раздел 6.6.4, «myisamchk — утилита обслуживания таблиц MyISAM».

Модификатор QUICK влияет на то, сливаются ли листья индекса при операциях удаления. DELETE QUICK наиболее полезен для приложений, где значения индекса для строк, удалённых ранее, заменяются похожими значениями индекса из строк, вставленных позднее. В этом случае, освобождённые позиции повторно используются.

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

В этой ситуации блоки индекса, связанные с удалёнными значениями индекса, становятся недостаточно заполненными, но не объединяются с другими блоками индекса из-за использования QUICK. Они остаются недостаточно заполненными, когда происходят новые вставки, потому что новые строки не имеют значений индекса в удалённом диапазоне. Кроме того, они остаются недостаточно заполненными даже если вы позже используете DELETE без QUICK, если только некоторые из удалённых значений индекса не окажутся в блоках индекса внутри или рядом с недостаточно заполненными блоками. Чтобы освободить неиспользуемое пространство индекса в этих обстоятельствах, используйте OPTIMIZE TABLE.

Если вы собираетесь удалить много строк из таблицы, может быть быстрее использовать DELETE QUICK, за которым следует OPTIMIZE TABLE. Это перестроит индекс вместо выполнения множества операций слияния блоков индекса.

Удаление из нескольких таблиц

Вы можете указать несколько таблиц в операторе DELETE, чтобы удалить строки из одной или нескольких таблиц в зависимости от условия в WHERE оператор. Вы не можете использовать ORDER BY или LIMIT в операторе DELETE для нескольких таблиц. Оператор table_references перечисляет таблицы, участвующие в соединении, как описано в Разделе 15.2.13.2, «Оператор JOIN».

В первом синтаксисе для нескольких таблиц удаляются только соответствующие строки из таблиц, перечисленных до FROM оператора. Во втором синтаксисе для нескольких таблиц удаляются только соответствующие строки из таблиц, перечисленных в FROM операторе (до USING оператора). Эффект заключается в том, что вы можете удалять строки из многих таблиц одновременно, а дополнительные таблицы используются только для поиска:

DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;

Или:

DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;

Эти операторы используют все три таблицы при поиске строк для удаления, но удаляют соответствующие строки только из таблиц t1 и t2.

В предыдущих примерах используется INNER JOIN, но операторы DELETE для нескольких таблиц могут использовать другие типы соединения, разрешённые в операторах SELECT, таких как LEFT JOIN. Например, чтобы удалить строки, существующие в t1, которые не имеют совпадений в t2, используйте LEFT JOIN:

DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;

Синтаксис допускает .* после каждого tbl_name для совместимости с Access.

Если вы используете оператор DELETE для нескольких таблиц, включающий InnoDB таблицы, для которых существуют ограничения внешнего ключа, оптимизатор MySQL может обрабатывать таблицы в порядке, отличающемся от их отношения родитель/ребёнок. В этом случае оператор завершается ошибкой и отменяется. Вместо этого, вы должны удалить из одной таблицы и полагаться на возможности ON DELETE, которые предоставляет InnoDB, чтобы заставить другие таблицы соответствующим образом измениться.

DELETE t1 FROM test AS t1, test2 WHERE ...

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

Правильно:

DELETE a1, a2 FROM t1 AS a1 INNER JOIN t2 AS a2
WHERE a1.id=a2.id;

DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2
WHERE a1.id=a2.id;

Неправильно:

DELETE t1 AS a1, t2 AS a2 FROM t1 INNER JOIN t2
WHERE a1.id=a2.id;

DELETE FROM t1 AS a1, t2 AS a2 USING t1 INNER JOIN t2
WHERE a1.id=a2.id;

Псевдонимы таблиц также поддерживаются для операторов DELETE с одной таблицей.