MySQL: DELETE удаление записи, TRUNCATE очистка данных таблицы
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 :
- Условия в необязательном операторе WHERE определяют, какие строки следует удалить. ВНИМАНИЕ! При отсутствии предложения WHERE будут удалены ВСЕ строки. Условие condition — это выражение, которое принимает значение TRUE для каждой удаляемой строки. Дополнительно смотрите материал «Использование инструкции WHERE в запросах к БД MySQL»
- Если указана инструкция ORDER BY , то строки удаляются в указанном порядке. ORDER BY можно применить к удалениям из одной таблицы, но не поддерживает удаления из нескольких таблиц.
- Инструкция LIMIT устанавливает ограничение на количество строк, которые могут быть удалены. LIMIT можно применить к удалениям из одной таблицы, но не к удалениям из нескольких таблиц.
- Модификатор 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();
Использование инструкции 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 .
- КРАТКИЙ ОБЗОР МАТЕРИАЛА.
- Функция connect() модуля MySQLdb
- Методы объекта Cursor модуля MySQLdb
- Исключения, определяемые модулем MySQLdb
- Реализация интерфейса MySQL C API в модуле MySQLdb Python
- Подмодуль times модуля MySQLdb
- Подмодуль converters модуля MySQLdb
- MySQL: Типы хранимых данных
- MySQL: CONVERT() и CAST(), преобразование типов
- MySQL: Неявные преобразования типов в вычислениях
- MySQL: Функции для работы со строками
- MySQL: Функции для работы с датой и временем
- MySQL: Временные интервалы и арифметика с датами
- MySQL: Математические функции
- MySQL: Агрегатные (групповые) функций
- MySQL: SELECT cоставление запросов
- MySQL: CASE и IF() в запросах SELECT
- MySQL: Поиск по шаблону, LIKE в запросах SELECT
- MySQL: Поиск по регулярному выражению
- MySQL: Использование WHERE в запросах
- MySQL: Использование GROUP BY и HAVING
- MySQL: LEFT JOIN и INNER JOIN объединение таблиц
- MySQL: UNION объединение запросов
- MySQL: UPDATE обновление данных таблицы
- MySQL: INSERT/REPLACE добавление данных в таблицу
- MySQL: DELETE/TRUNCATE удаление записи/очистка данных таблицы
- MySQL: CREATE TABLE создание таблиц
- Импорт CSV-файла в MySQL таблицу, экспорт данных в CSV
- MySQL: ALTER TABLE изменение таблицы
- MySQL: Хранимые процедуры и функции
- MySQL: События EVENT и планировщик событий
- MySQL: CREATE/ALTER USER и права GRANT/ROLE
- MySQL: Сброс пароля root на ОС Linux/Windows
- Логирование ВСЕХ и/или МЕДЛЕННЫХ запросов к БД MYSQL
- Кэширование запросов на MySQL-сервере
- Как конвертировать БД MySQL в требуемую кодировку
DELETE оператор MySQL
MySQL оператор DELETE используется для удаления одной записи или нескольких записей из таблицы в MySQL.
Синтаксис
Простой синтаксис оператора DELETE в MySQL:
DELETE FROM table
[WHERE conditions];
Теперь полный синтаксис оператора DELETE в MySQL:
DELETE [ LOW_PRIORITY ] [ QUICK ] [ IGNORE ] FROM table
[WHERE conditions]
[ORDER BY expression [ ASC | DESC ]]
[LIMIT number_rows];
Параметры или аргументы
LOW_PRIORITY — необязательный. Если указан LOW_PRIORITY , удаление будет задерживаться до тех пор, пока не будет никаких процессов затрагивающих таблицу. LOW_PRIORITY может использоваться с таблицами MyISAM, MEMORY и MERGE, которые используют блокировку на уровне таблицы.
QUICK — необязательный. Если указан QUICK , в процессе удаления не удаляются листы индекса, что делает удаление быстрее для таблиц MyISAM
IGNORE — необязательный. Если указан IGNORE , все ошибки, возникающие во время удаления, игнорируются. IGNORE был представлен в MySQL 4.1.1.
table — таблица, из которой вы хотите удалить записи.
WHERE conditions — необязательный. Условия, которые должны быть выполнены для записей, подлежащих удалению. Если условий не предусмотрено, все записи из таблицы будут удалены.
ORDER BY expression — необязательный. Он может использоваться в сочетании с LIMIT для сортировки записей соответствующим образом при ограничении количества удаляемых записей.
LIMIT — необязательный. Если LIMIT указан, то он контролирует максимальное количество записей для удаления из таблицы. В лучшем случае количество записей, заданных number_rows , будет удалено из таблицы.
Примечание
- Вам не нужно указывать поля в инструкции DELETE MySQL, так как вы удаляете всю строку из таблицы.
Пример с одним условием
Рассмотрим пример запроса MySQL DELETE, в котором у нас есть только одно условие в операторе DELETE.
Например:
Как очистить таблицу в MySQL
В статье мы расскажем, как в MySQL очистить таблицу. Мы покажем два способа и объясним разницу между ними.
Очистить таблицу можно при помощи одной из команд:
Разница между TRUNCATE и DELETE
Команда TRUNCATE является оператором DDL. DDL (Data Definition Language) — это язык определения данных. Операторы языка DDL управляют объектами баз данных: удаляют, создают или переименовывают объекты БД.
Команда DELETE является DML-оператором. DML (Data Manipulation Language) — это язык манипуляции данными. Операторы языка DML позволяют вставить, удалить, изменить, извлечь или обновить данные в базе.
Сравним работу команд:
| TRUNCATE | DELETE |
|---|---|
| Удаляет все данные из таблицы | Может удалить часть данных в соответствии с условием WHERE |
| Удаляет все строки из таблицы освобождением страниц | Удаляет строки по одной |
| Записывает в журнал транзакций сведения о каждой удалённой странице, а не строке | Делает запись в журнал транзакций при удалении каждой строки |
| Работает быстрее | Работает медленнее |
| Нужны привилегии ALTER | Нужны привилегии DELETE |
| Сбрасывает идентификаторы | Не сбрасывает идентификаторы |
| Блокирует таблицу и страницу перед удалением | Блокирует строку перед её удалением |
Выбор команды зависит от конкретного случая. Если нужно удалить некоторые строки по условию, подойдёт только DELETE. Если нужно полностью очистить таблицу и сбросить идентификаторы, используйте TRUNCATE.
Понять разницу между командами и определиться с их выбором поможет таблица с операторами каждого языка:
| DDL | DML |
|---|---|
| CREATE | SELECT |
| ALTER | INSERT |
| DROP | UPDATE |
| TRUNCATE | DELETE |
| COMMENT | MERGE |
| RENAME | CALL |
| EXPLAIN PLAN | |
| LOCK TABLE |
Таким образом, операторы DDL управляют структурой, а операторы DML — её содержимым.
Как в MySQL очистить таблицу
Подключитесь к серверу по SSH. Затем подключитесь к MySQL при помощи команды:
mysql -u username -p’password’Вместо username введите имя пользователя, вместо password — пароль.
Если вы не знаете пароль, попробуйте войти без него при помощи команды:
mysqlЕсли подключение без пароля не настроено, возникнет ошибка:
В этом случае сбросьте пароль от root-пользователя MySQL по инструкции.
TRUNCATE
TRUNCATE полностью очищает таблицу без возможности указать дополнительные условия. Для этого:
Выберите базу данных, в которой находится таблица, которую вы хотите очистить:
USE db_name;Вместо db_name введите имя базы данных.
Очистите таблицу при помощи команды:
TRUNCATE table_name;TRUNCATE позволяет указать название БД и название таблицы в одном запросе. Для этого используйте команду:
TRUNCATE db_name.table_name;Вместо db_name введите имя базы данных, а вместо table_name введите имя таблицы.
Готово, вы очистили таблицу.
DELETE
Выберите базу данных, в которой находится таблица, которую вы хотите очистить:
USE db_name;Вместо db_name введите имя базы данных.
Очистите таблицу при помощи команды:
DELETE FROM table_name;Вы можете указать название БД и таблицы в одном запросе:
DELETE FROM db_name.table_name;Вместо db_name введите имя базы данных, а вместо table_name введите имя таблицы.
Готово, вы очистили таблицу при помощи DELETE.
DELETE позволяет использовать условие WHERE, чтобы удалить некоторые строки из таблицы:
DELETE FROM table_name WHERE condition;
Где condition — это условие.
Пример команды, в котором будут удалены все строки, значение столбца, id которых больше 1000:
DELETE FROM table_name WHERE id > 1000;
Как проверить содержимое таблицы
Если вы хотите убедиться, что в таблице не осталось данных, воспользуйтесь командой:
SELECT * FROM db_name.table_name;Вместо db_name введите имя базы данных, а вместо table_name введите имя таблицы.
Если таблица пустая, вывод будет следующим:
Помогла ли вам статья?
Спасибо за оценку. Рады помочь ��
Команда SQL для удаления и обновление данных в базе (DELETE, UPDATE)
В базу данных можно не только добавлять данные, но и удалять их оттуда. Ещё существует возможность обновить данные в базе. Рассмотрим оба случая.
Удаление данных из базы (DELETE)
Удаление из базы данных происходит с помощью команды «DELETE» (переводится с английского как «УДАЛИТЬ»). Функция удаляет не одну строку, а несколько, при этом выбирает для удаления строки по логике функции «SELECT». То есть чтобы удалить данные из базы, необходимо точно определить их. Приведём пример SQL команды для удаления одной строчки:
DELETE FROM `USERS` WHERE `ID` = 2 LIMIT 1;Благодаря этому запросу из таблицы «USERS» будет удалена одна запись, у которой в столбце «ID» стоит значение «2».
Обратите внимание, что в конце запроса стоит лимит на выборку «LIMIT 1;» размером в 1 строку. Его можно было не ставить, если поле «ID» является «PRIMARY KEY» (первичный ключ, то есть содержит только уникальные значения). Но всё-таки рекомендуем ставить ограничение «LIMIT 1;» в любом случае, если вы намерены удалить только одну строчку.
Теперь попробуем удалить сразу диапазон данных. Для этого достаточно составить запрос, в результате выполнения которого вернётся несколько строк. Все эти строки будут удалены:
DELETE FROM `USERS` WHERE `ID` >= 5;Этот запрос удалит все строки в таблицы, у которых в столбце «ID» стоит значение меньше 5. Если не поставить никакого условия «WHERE» и лимита «LIMIT», то будут удалены абсолютно все строки в таблице:
DELETE FROM `USERS`;На некоторых версиях MySQL способ удаления всех строк через «DELETE FROM _;» может работать медленнее, чем «TRUNCATE _;«; Поэтому для очистки всей таблицы лучше всё-таки использовать «TRUNCATE».
Обновление данных в базе (UPDATE)
Функция обновления «UPDATE» (переводится с английского как «ОБНОВИТЬ») довольно часто используется в проектах сайтов. Как и в случае с функцией «DELETE», фкнция обновления не успокоится до тех пор, пока не обновит все поля, которые подходят под условия, если нет лимита на выборку. Поэтому необходимо задавать однозначные условия, чтобы вместо одной строки нечаянно не обновить половину таблицы. Приведём пример использования команды «UPDATE»:
UPDATE `USERS` SET `NAME` = 'Мышь' WHERE `ID` = 3 LIMIT 1;В этом примере, в таблие «USERS» будет установлено значение «Мышь» в столбец «NAME» у строки, в столбце «ID» которой стоит значение «3». Можно обновить сразу несколько столбцов у одной записи, передав значения через запятую. Попробуем обновить не только значение с толбце «NAME», но и значение в столбце «FOOD» используя один запрос:
UPDATE `USERS` SET `NAME` = 'Мышь', `FOOD` = 'Сыр' WHERE `ID` = 3 LIMIT 1;Если не поставить никаких лимитов LIMIT и условий WHERE, то все записи таблицы будут обновлены без исключений.