Очистка таблиц MySQL: 3 способа удалить все данные быстро и безопасно
Вы случайно залили тестовыми данными рабочую базу? Или просто хотите мгновенно очистить огромную таблицу, не удаляя её структуру? Остановитесь прямо сейчас! Неправильный подход к очистке может привести к долгому ожиданию, ошибкам с внешними ключами и даже потере целостности данных.
В мире MySQL есть несколько путей удалить все записи из таблицы, и выбор между ними — это не просто вопрос вкуса. Команды DELETE и TRUNCATE, хоть и делают на первый взгляд одно и то же, работают совершенно по-разному под капотом.
Одна блокирует каждую строку и пишет в лог, а другая действует молниеносно, но имеет серьёзные ограничения. А что делать, если система ругается на внешние ключи и не даёт ничего удалить? Отключать проверки — опасное решение, о последствиях которого вы обязательно должны знать.
В этом материале мы не просто покажем синтаксис команд. Вы узнаете, какой метод выбрать для быстрой очистки больших таблиц, как безопасно обойти ограничения внешних ключей и какой единственный параметр позволяет настроить каскадное удаление. Мы разберём всё на конкретных примерах, чтобы вы могли применить эти знания сразу после прочтения.
Готовы навсегда разобраться с очисткой таблиц и делать это быстро, осознанно и без ошибок? Тогда начинаем!
В MySQL**, как и в других СУБД можно очищать таблицы. Очистка таблицы позволяет удалять данные при этом не затрагивая саму структуру таблицы. В MySQL существует несколько способов очистки таблицы. В частности, можно выделить очистку таблицы при помощи команд DELETE и TRUNCATE.
Обе команды выполняют одну и ту же задачу, но имеют несколько отличий о которых будет рассказано далее в статье. Также будет упомянуто об удалении данных из таблицы при наличии внешних ключей (foreign key). В данной статье будет рассмотрено как очистить таблицу в MySQL различными способами в операционной системе Ubuntu 20.04.
Содержание статьи
Как очистить таблицу в MySQL
Для очистки таблицы в MySQL существует несколько способов. Далее будут рассмотрены все возможные способы.
1. Удаление данных таблицы с помощью DELETE
Для удаления данных из таблицы можно воспользоваться инструкцией DELETE которая может удалять строки таблицы по заданному условию. Предположим, есть таблица MyGuests в которой есть пользователь John:
При помощи оператора DELETE и заданного условия - в данном случае удаление будет происходит по столбцу firstname который принимает значение имени пользователя будет удалена запись с номером (id) 1 и именем John:
DELETE FROM MyGuests WHERE firstname = 'John';
Также оператор DELETE может удалять все строки таблицы сразу. В качестве примера есть таблица с двумя записями:
Удалим все строки за один раз не задавая никаких условий. Для этого необходимо выполнить следующий SQL запрос:
DELETE FROM MyGuests;
На этом очистка таблицы MySQL завершена.
2. Удаление данных таблицы с помощью TRUNCATE
Также для удаления всех строк в таблице существует специальная команда TRUNCATE. Она схожа с DELETE, однако он не позволяет использовать WHERE. Также стоит выделить следующие особенности при очистки таблицы MySQL с помощью оператора TRUNCATE:
- TRUNCATE не позволяет удалять отдельные строки;
- DELETE блокирует каждую строку, а TRUNCATE всю таблицу;
- TRUNCATE нельзя использовать с таблицами содержащими внешние ключи других таблиц;
- После использования оператора TRUNCATE в консоль не выводится информация о количестве удаленных строк из таблицы.
В качестве примера возьмем таблицу с двумя записями из предыдущего примера:
Для очистки этой таблицы MySQL от всех записей необходимо выполнить следующий SQL запрос:
TRUNCATE MyGuests;
Как уже было упомянуто ранее команда TRUNCTE не выводит количество удалённых строк в таблице поэтому в консоль был выведен текст 0 rows affected.
Как очистить таблицу с Foreign Key Constraint
Если в таблице присутствуют внешние ключи (Foreign Key) то просто так очистить таблицу не получится. Предположим, есть 2 таблицы - Equipment и EquipmentCategory:
В таблице Equipment присутствует столбец с именем category_id, который связан внешним ключом со столбцом id в другой таблице - EquipmentCategory. Например:
Equipment:
- id
- category_id
- name
EquipmentCategory:
- id
- name
Если попытаться очистить все строки таблицы EquipmentCategory при помощи оператора TRUNCATE то будет выведена следующая ошибка:
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`Inventory`.`Equipment`, CONSTRAINT `Equipment_ibfk_1`)
Данная ошибка говорит о том, что таблица ссылается на другую таблицу и имеет ограничение на удаление в виде внешнего ключа. Чтобы избежать данной ошибки можно воспользоваться отключением проверки внешних ключей и добавлением параметра ON DELETE CASCADE при создании таблицы.
1. Отключение проверки внешних ключей
Чтобы очистить таблицу при наличии в ней внешних ключей можно отключить проверку внешних ключей. Для этого нужно выполнить следующую команду:
SET FOREIGN_KEY_CHECKS=0;
Данная команда отключит проверку внешних ключей и тем самым позволит очистить таблицу при помощи команды TRUNCATE:
TRUNCATE Equipment;
Также очистить таблицу можно и при помощи DELETE:
DELETE FROM Equipment;
После очистки таблицы можно вернуть проверку внешних ключей при помощи команды:
SET FOREIGN_KEY_CHECKS=1;
Однако такой способ использовать не рекомендуется, потому что вы теряете консистентность данных в базе данных и программа использующая её может работать некорректно. Есть и другое решение. При удалении таблицы удалять все связанные с ней записи в других таблицах.
2. Добавление опции ON DELETE CASCADE
Опция ON DELETE CASCADE используется для неявного удаления строк из дочерней таблицы всякий раз, когда строки удаляются из родительской таблицы. Опция ON DELETE CASCADE её можно задать при создании таблицы, однако если вы этого не сделали, то можно удалить CONSTRAINT и создать его заново.
Для просмотра информации о внешнем ключе содержащемся в таблице необходимо выполнить команду SHOW CREATE TABLE:
SHOW CREATE TABLE EquipmentCategory;
В данном примере таблица называется EquipmentCategory и в ней присутствует константа с именем EquipmentCategory_ibfk_1.
Далее необходимо удалить внешний ключ при помощи команд ALTER TABLE и DROP. Например, если EquipmentCategory - имя таблицы, а EquipmentCategory_ibfk_1 - имя внешнего ключа выполните:
ALTER TABLE EquipmentCategory DROP FOREIGN KEY EquipmentCategory_ibfk_1;
После этого внешний ключ необходимо вернуть обратно добавив к нему опцию ON DELETE CASCADE. Команда будет следующей:
ALTER TABLE EquipmentCategory ADD FOREIGN KEY (category_id) references EquipmentCategory(id) on DELETE CASCADE;
После этого таблицу можно очистить при помощи команды TRUNCATE:
TRUNCATE EquipmentCategory;
Выводы
В данной статье было рассмотрено как очистить таблицу MySQL от данных. Были рассмотрены команды DELETE и TRUNCATE а также созданы таблицы с наполненной информацией для демонстрации удаления информации. Если у вас остались вопросы задавайте их в комментариях!