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

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

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

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

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

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

where_condition — это выражение, которое вычисляется как истинное для каждой строки, подлежащей удалению. Оно задаётся так, как описано в разделе 13.2.9 «Оператор 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 на таблицу, чтобы удалять строки из неё. Вам необходимо только право SELECT на любые столбцы, которые только читаются, такие как те, которые указаны в предложении WHERE.

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

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

Скорость операций удаления также может зависеть от факторов, обсуждаемых в разделе 8.2.4.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.

Дополнительную информацию и примеры см. в разделе 22.5 «Выбор разбиений».

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

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

Для таблиц MyISAM вы можете указать вторичный столбец AUTO_INCREMENT в ключе из нескольких столбцов. В этом случае повторное использование значений, удалённых из начала последовательности, происходит даже для таблиц MyISAM. См. раздел 3.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, поэтому операция переименования не подвержена проблемам конкурентности. См. раздел 13.1.33 «Оператор RENAME TABLE».

Таблицы MyISAM

В таблицах MyISAM удалённые строки сохраняются в связанном списке, и последующие операции вставки используют старые позиции строк. Чтобы освободить неиспользуемое пространство и уменьшить размеры файлов, используйте оператор OPTIMIZE TABLE или утилиту myisamchk для реорганизации таблиц. Оператор OPTIMIZE TABLE проще в использовании, но myisamchk быстрее. См. раздел 13.7.2.4 «Оператор OPTIMIZE TABLE» и раздел 4.6.3 «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 перечисляет таблицы, участвующие в объединении, как описано в разделе 13.2.9.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;