Какая инструкция удаляет все строки таблицы более быстро чем инструкция delete

В этом уроке вы узнаете, как быстро удалить все строки из таблицы MySQL с помощью SQL запроса.

Удаление данных таблицы

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

Оператор TRUNCATE TABLE удаляет все строки из таблицы, но структура таблицы и ее столбцы, ограничения, индексы и т.д. остаются неизменными. Чтобы удалить определение таблицы в дополнение к ее данным, вы можете использовать оператор DROP TABLE.

Синтаксис

Основной синтаксис TRUNCATE TABLE может быть задан с помощью:

TRUNCATE TABLE table_name;

Предположим, у нас есть таблица сотрудников в нашей базе данных со следующими записями:

+--------+--------------+------------+--------+---------+
| emp_id | emp_name     | hire_date  | salary | dept_id |
+--------+--------------+------------+--------+---------+
|      1 | Vasya Pupkin | 2001-05-01 |   5000 |       4 |
|      2 | Vanya Pupkin | 2002-07-15 |   6000 |       2 |
+--------+--------------+------------+--------+---------+

блок 1

Следующая команда удаляет все строки из таблицы employees:

TRUNCATE TABLE employees;

Теперь, после выполнения вышеприведенной команды SQL, если вы попытаетесь выбрать записи из таблицы сотрудников, вы получите пустой набор результатов.

TRUNCATE TABLE в сравнении с DELETE

Хотя DELETE и TRUNCATE TABLE, имеют одинаковый эффект — они работают по-разному. Вот некоторые основные различия между этими двумя утверждениями:

Оператор TRUNCATE TABLE удаляет и заново создает таблицу таким образом, чтобы любое значение автоинкремента сбрасывается до начального значения, которое обычно равно 1.
DELETE позволяет фильтровать, какие строки должны быть удалены, на основе необязательного условия WHERE, тогда как TRUNCATE TABLE не поддерживает условие WHERE, а просто удаляет все строки.
TRUNCATE TABLE работает быстрее и использует меньше системных ресурсов, чем DELETE, потому что DELETE сканирует таблицу, чтобы сгенерировать число затронутых строк, затем удаляет строки по одной и записывает запись в журнал базы данных для каждой удаленной строки, тогда как TRUNCATE TABLE просто удалить все строки без предоставления дополнительной информации.

блок 3

Источник

I discovered the TRUNCATE TABLE in the msdn transact-SQL reference. For all interested here are the remarks:

TRUNCATE TABLE is functionally identical to DELETE statement with no WHERE clause: both remove all rows in the table. But TRUNCATE TABLE is faster and uses fewer system and transaction log resources than DELETE.

The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log.

TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column. If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.

You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; instead, use DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.

TRUNCATE TABLE may not be used on tables participating in an indexed view.

Источник

Here is a summary of some important differences between these sql commands:

sql truncate command:

1) It is a DDL (Data Definition Language) command, therefore commands such as COMMIT and ROLLBACK do not apply to this command (the exceptions here are PostgreSQL and MSSQL, whose implementation of the TRUNCATE command allows the command to be used in a transaction)

2) You cannot undo the operation of deleting records, it occurs automatically and is irreversible (except for the above exceptions — provided, however, that the operation is included in the TRANSACTION block and the session is not closed). In case of Oracle — Includes two implicit commits, one before and one after the statement is executed. Therefore, the command cannot be withdrawn while a runtime error will result in commit anyway

3) Deletes all records from the table, records cannot be limited to deletion. For Oracle, when the table is split per partition, individual partitions can be truncated (TRUNCATE) in isolation, making it possible to partially remove all data from the table

4) Frees up the space occupied by the data in the table (in the TABLESPACE — on disk). For Oracle — if you use the REUSE STORAGE clause, the data segments will not be rolled back, i.e. you will keep space from the deleted rows allocated to the table, which can be a bit more efficient if the table is to be reloaded with data. The high mark will be reset

5) TRUNCATE works much faster than DELETE

6) Oracle Flashback in the case of TRUNCATE prevents going back to pre-operative states

7) Oracle — TRUNCATE cannot be granted (GRANT) without using DROP ANY TABLE

The TRUNCATE operation makes unusable indexes usable again

9) TRUNCATE cannot be used when the enabled foreign key refers to another table, then you can:

execute the command: DROP CONSTRAINT, then TRUNCATE, and then play it through CREATE CONSTRAINT or
execute the command: SET FOREIGN_KEY_CHECKS = 0; then TRUNCATE, then: SET_FOREIGN_KEY_CHECKS = 1;

10) TRUNCATE requires an exclusive table lock, therefore, turning off exclusive table lock is a way to prevent TRUNCATE operation on the table

11) DML triggers do not fire after executing TRUNCATE (so be very careful in this case, you should not use TRUNCATE, if a delete trigger is defined in the table to perform an automatic table cleanup or a logon action after row deletion). On Oracle, DDL triggers are fired

12) Oracle — TRUNCATE cannot be used in the case of: database link
13) TRUNCATE does not return the number of records deleted

14) Transaction log — one log indicating page deallocation (removes data, releasing allocation of data pages used for storing table data and writes only page deallocations to the transaction log) — faster execution than DELETE. TRUNCATE only needs to adjust the pointer in the database to the table (High Water Mark) and the data is immediately deleted, therefore it uses less system resources and transaction logs

15) Performance (acquired lock) — table and page lock — does not degrade performance during execution

16) TRUNCATE cannot be used on tables involved in transactional replication or merge replication

sql delete command:

1) It is a DML (Data Manipulation Language) command, therefore the following commands are used for this command: COMMIT and ROLLBACK

2) You can undo the operation of removing records by using the ROLLBACK command

3) Deletes all or some records from the table, you can limit the records to be deleted by using the WHERE clause

4) Does not free the space occupied by the data in the table (in the TABLESPACE — on the disk)

5) DELETE works much slower than TRUNCATE

6) Oracle Flashback works for DELETE

7) Oracle — For DELETE, you can use the GRANT command

The DELETE operation does not make unusable indexes usable again

9) DELETE in case foreign key enabled refers to another table, can (or not) be applied depending on foreign key configuration (if not), please:

execute the command: DROP CONSTRAINT, then TRUNCATE, and then play it through CREATE CONSTRAINT or
execute the command: SET FOREIGN_KEY_CHECKS = 0; then TRUNCATE, then: SET_FOREIGN_KEY_CHECKS = 1;

10) DELETE requires a shared table lock

11) Triggers fire

12) DELETE can be used in the case of: database link

13) DELETE returns the number of records deleted

14) Transaction log — for each deleted record (deletes rows one at a time and records an entry in the transaction log for each deleted row) — slower execution than TRUNCATE. The table may still contain blank pages after executing the DELETE statement. DELETE needs to read records, check constraints, update block, update indexes, and generate redo / undo. All of this takes time, hence it takes time much longer than with TRUNCATE

15) Performance (acquired lock) — record lock — reduces performance during execution — each record in the table is locked for deletion

16) DELETE can be used on a table used in transactional replication or merge replication

Источник

В базу данных можно не только добавлять данные, но и удалять их оттуда. Ещё существует возможность обновить данные в базе. Рассмотрим оба случая.

Удаление данных из базы (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, то все записи таблицы будут обновлены без исключений.

Была ли эта статья полезна?

Есть вопрос?

Закажите недорогой хостинг

Заказать

всего от 290 руб

Источник

В статье мы расскажем, как в MySQL очистить таблицу. Мы покажем два способа и объясним разницу между ними.

Очистить таблицу можно при помощи одной из команд:

TRUNCATE,
DELETE.

Разница между 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 — пароль.

Если вы не знаете пароль, попробуйте войти без него при помощи команды:

Если подключение без пароля не настроено, возникнет ошибка:

В этом случае сбросьте пароль от root-пользователя MySQL по инструкции.

TRUNCATE

TRUNCATE полностью очищает таблицу без возможности указать дополнительные условия. Для этого:

1.

Выберите базу данных, в которой находится таблица, которую вы хотите очистить:

Вместо db_name введите имя базы данных.
2.
Очистите таблицу при помощи команды:

TRUNCATE позволяет указать название БД и название таблицы в одном запросе. Для этого используйте команду:
```
TRUNCATE db_name.table_name;
```
Вместо db_name введите имя базы данных, а вместо table_name введите имя таблицы.

Готово, вы очистили таблицу.

DELETE

1.

Выберите базу данных, в которой находится таблица, которую вы хотите очистить:

Вместо db_name введите имя базы данных.
2.
Очистите таблицу при помощи команды:

Вы можете указать название БД и таблицы в одном запросе:
```
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 введите имя таблицы.

Если таблица пустая, вывод будет следующим:

Источник