Сообщить об ошибке.

MySQL: DELETE удаление записи, TRUNCATE очистка данных таблицы.

Содержание:


DELETE удаление записи/строки из таблицы MySQL.

MySQL-инструкция DELETE удаляет строки из таблицы и возвращает количество удаленных строк. Чтобы проверить количество удаленных строк, необходимо вызвать функцию SELECT ROW_COUNT(). Аналогичная функция Python модуля MySQLdb - Cursor.rowcount.

Синтаксис инструкции DELETE.

-- удаление строк/записей из одной таблицы 
DELETE [IGNORE] FROM tbl_name [AS tbl_alias]
WHERE condition
ORDER BY ... [ASC|DESC]
LIMIT count

Комментарии к синтаксису DELETE:

  1. Условия в необязательном операторе WHERE определяют, какие строки следует удалить. ВНИМАНИЕ! При отсутствии предложения WHERE будут удалены ВСЕ строки. Условие condition - это выражение, которое принимает значение TRUE для каждой удаляемой строки. Дополнительно смотрите материал "Использование инструкции WHERE в запросах к БД MySQL"

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

  3. Инструкция LIMIT устанавливает ограничение на количество строк, которые могут быть удалены. LIMIT можно применить к удалениям из одной таблицы, но не к удалениям из нескольких таблиц.

  4. Модификатор IGNORE заставляет MySQL игнорировать ошибки во время процесса удаления строк. (Ошибки, возникшие на этапе синтаксического анализа, обрабатываются обычным способом.) Ошибки, которые игнорируются из-за использования IGNORE, возвращаются как предупреждения.

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

Использование WHERE при удалении записей.

Условия WHERE определяет, какие строки следует удалить, а при его отсутствии будут удалены ВСЕ строки. Условие condition - это выражение, которое принимает значение TRUE для каждой удаляемой строки.

Например стоит задача: удаления записей о продажах за определенное число или очистка старых (ненужных) данных из таблицы sales:

-- удаление старых (ненужных) записей о продажах до '2001-07-09'
DELETE FROM sales WHERE date_sales < '2001-07-09';
-- удаления записей о продажах за сегодня
DELETE FROM sales WHERE date_sales=NOW();

Дополнительно смотрите материал "Использование инструкции WHERE в запросах к БД MySQL"

Использование инструкции ORDER BY и LIMIT.

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

-- подсчитываем кол-во строк в таблице `logs`
SET @count = SELECT count(id) FROM logs;
-- удаляем все, кроме 500 последних добавленных строк
DELETE FROM logs WHERE @count > 500 
ORDER BY id ASC LIMIT @count - 500;

Или, наверное, можно так (не проверяли):

-- оставит последние 500 строк, если журнал 
-- таблицы `logs` не превышает 10000000 записей 
DELETE FROM logs ORDER BY id DESC LIMIT 500, 10000000;

Более подробно об использование инструкций ORDER BY и LIMIT смотрите в материале "Составление запросов SELECT к БД MySQL"

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

Удаление информации - вещь ответственная. Неправильно поставленный запрос может обернуться потерей критических данных. Рекомендуем редко пользоваться запросами DELETE из нескольких таблиц. Если такой запрос необходим в целях оптимизации производительности, сначала постройте подобный запрос с использованием инструкции SELECT, а потом просто замените SELECT на DELETE, а также уберите из запроса извлекаемые столбцы.

-- проверочный запрос `SELECT` 
SELECT tbl_a.col, tbl_b.col FROM tbl_a , tbl_b
    USING tbl_a.id = tbl_b.id
    WHERE condition

-- если проверочный запрос выдал 
-- те строки, которые необходимо удалить, 
-- то переделываем его на `DELETE`
DELETE FROM tbl_a , tbl_b
    USING tbl_a.id = tbl_b.id
    WHERE condition

TRUNCATE TABLE полная очистка таблицы MySQL.

Пример очистки таблицы animals от всех записей:

TRUNCATE TABLE animals

Хотя TRUNCATE TABLE похож на инструкцию DELETE, он отличается следующим:

  • Операции TRUNCATE удаляют и заново создают таблицу, что намного быстрее, чем удаление строк по одной, особенно для больших таблиц.
  • Операции TRUNCATE вызывают неявную фиксацию, поэтому их нельзя откатить.
  • Операции TRUNCATE не могут быть выполнены, если сеанс удерживает активную блокировку таблицы.
  • TRUNCATE TABLE терпит неудачу для таблицы InnoDB, если есть какие-либо ограничения FOREIGN KEY из других таблиц, которые ссылаются на таблицу. Допускаются ограничения внешнего ключа между столбцами одной и той же таблицы.
  • Операции TRUNCATE не возвращают значимого значения количества удаленных строк. Обычный результат - "0 rows affected", что следует интерпретировать как "нет информации".
  • Любое значение AUTO_INCREMENT сбрасывается до своего начального значения. Это верно даже для MyISAM и InnoDB, которые обычно не используют повторно значения последовательности.
  • Оператор TRUNCATE TABLE не вызывает триггеры ON DELETE, установленные для внешнего ключа FOREIGN KEY.