Статья: Удаление данных в SQL
Способы удаления записей
Для удаления записей из таблиц в MySQL существует две команды:
- TRUNCATE;
- DELETE.
Команда TRUNCATE имеет следующий синтаксис:
TRUNCATE TABLE имя_таблицы;
Если нужно полностью очистить таблицу tovar, то следует выполнить команду:
TRUNCATE TABLE tovar;
Команда TRUNCATE позволяет очистить таблицу сразу и полностью и не позволяет удалять отдельные строки, удовлетворяющие каким-либо условиям.
Команда DELETE удаляет из таблицы записи, удовлетворяющие некоторому условию. Она выполняет удаление двух видов:
- Удаление из одной таблицы;
- Каскадное удаление из нескольких таблиц.
Удаление из одной таблицы
DELETE [LOW_PRIORITY | QUICK] FROM имя_таблицы
[WHERE условие ]
[ORDER BY ...]
[LIMIT число_записей];
- Если указана инструкция LOW_PRIORITY, то удаление не произойдет, пока все пользователи не завершат чтение таблицы.
- Инструкция QUICK запрещает объединять индексы при выполнении удаления. Это несколько ускоряет процесс удаления.
- Инструкция ORDER BY позволяет упорядочить записи перед удалением. Сама по себе эта опция не имеет смысла. Ее полезно использовать в сочетании с инструкцией LIMIT.
- Инструкция LIMIT задает количество удаляемых записей.
Для удаления из таблицы tovar всех товаров, которые имеют цену выше 300р. Нужно выполнить команду:
DELETE FROM Tovar WHERE price>300;
Если в таблице Tovar присутствует поле timestamp, которое хранит время добавления каждой записи, то можно удалить самый старый товар с ценой больше 300р.:
DELETE FROM Tovar WHERE price>300 ORDER BY timestamp LIMIT (1);
Каскадное удаление
При каскадном удалении удаляется не только основная запись, но и записи из других таблиц, содержащие внешний ключ основной записи. В MySQL существует два вида таблиц:
- InnoDB, которые поддерживают механизм связи по внешним ключам.
- MyISAM, которые не поддерживают механизма связи по внешним ключам. В этом случае все связи хранятся исключительно в памяти администратора базы данных и программистов.
Для таблиц InnoDB при построении внешнего ключа всегда описывается реакция СУБД на удаление связанных записей. Реакция может быть трех видов:
- RESTRICT – ограничение;
- CASCADE – каскадное удаление;
- SET NULL – установить значение NULL вместо значений удаленного внешнего ключа.
Пусть имеется таблица tovar, содержащая внешний ключ - categ со ссылкой на таблицу category.
Таким образом, каждый товар относится к определенной категории:
Необходимо удалить категорию «Лакокрасочные» из таблицы category и каскадно удалить все связанные с ней записи из таблицы tovar. Если во время создания внешнего ключа categ была указана реакция CASCADE при удалении, то для решения задачи достаточно выполнить команду:
DELETE FROM category WHERE category.categ_name=”лакокрасочные”;
Все товары, относящиеся к категории «лакокрасочные» автоматически удалятся из связанной таблицы tovar.
Однако, если пользователь работает с таблицами MyIsam, то СУБД не знает ни о каких внешних ключах и каскадного удаления не произойдет. В этом случае пригодятся специальные формы команды DELETE, предназначенные для каскадного удаления:
DELETE [LOW_PRIORITY | QUICK] имя_таблицы [ ,имя_таблицы ...]
FROM таблица_ссылка
[WHERE условие]
или
DELETE [LOW_PRIORITY | QUICK]
FROM имя_таблицы, [имя_таблицы ...]
USING таблица_ссылка
[WHERE условие]
Задачу из примера 4 можно решить двумя эквивалентными командами:
DELETE tovar, category FROM tovar join category on category.itcateg=tovar.categ where category.name_categ=”Лакокрасочные”;
DELETE from tovar, category using tovar join category on category.itcateg=tovar.categ where category.name_categ=”Лакокрасочные”;
На первый взгляд кажется, что команды
TRUNCATE имя_таблицы;
и
DELETE FROM имя_таблицы;
совершенно эквивалентны. Действительно, они обе полностью очищают таблицу от всех записей. Однако, разница все же есть.
Если используется TRUNCATE, то удаляются сразу все записи и при повторном заполнении таблицы поля с автоинкрементом получат значения начиная с 1. Если используется DELETE, то удаление происходит по одной записи и при повторном заполнении таблицы поля с автоинкрементом получат значение на 1 больше последнего удаленного значения (то есть автоинкрементирование продолжится).