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.39, «Оператор 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, «Выбор разбиений».

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

Если вы удалите строку, содержащую максимальное значение для столбца с автоинкрементом, значение не будет повторно использовано для таблицы 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.38, «Оператор 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 с одной таблицей.