mysql/sqlserver 中截断与删除的比较

comparison of truncate vs delete in mysql/sqlserver

提问by bgs

One thing struck in my head about mysql/sqlserver i.e delete/ truncate

关于 mysql/sqlserver 的一件事让我印象深刻，即删除/截断

Which one is better and faster ?

哪个更好更快？

where to use delete?

在哪里使用删除？

where to use truncate?

在哪里使用截断？

回答by bgs

DELETE

删除

1. DELETE is a DML Command.
2. DELETE statement is executed using a row lock, each row in the table is locked for deletion.
3. We can specify filters in where clause
4. It deletes specified data if where condition exists.
5. Delete activates a trigger because the operation are logged individually.
6. Slower than truncate because, it keeps logs.
7. Rollback is possible.

1. DELETE 是一个 DML 命令。
2. DELETE 语句使用行锁执行，表中的每一行都被锁定以进行删除。
3. 我们可以在 where 子句中指定过滤器
4. 如果 where 条件存在，它会删除指定的数据。
5. 删除会激活触发器，因为操作是单独记录的。
6. 比截断慢，因为它保留日志。
7. 回滚是可能的。

TRUNCATE

截短

1. TRUNCATE is a DDL command.
2. TRUNCATE TABLE always locks the table and page but not each row.
3. Cannot use Where Condition.
4. It Removes all the data.
5. TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.
6. Faster in performance wise, because it doesn't keep any logs.
7. Rollback is possible.

1. TRUNCATE 是一个 DDL 命令。
2. TRUNCATE TABLE 总是锁定表和页，但不是每一行。
3. 不能使用 Where 条件。
4. 它删除所有数据。
5. TRUNCATE TABLE 无法激活触发器，因为该操作不记录单个行删除。
6. 在性能方面更快，因为它不保留任何日志。
7. 回滚是可能的。

• DELETE and TRUNCATE both can be rolled back when used with TRANSACTION (TRUNCATE can be rolled back in SQL Server, but not in MySQL).
• if there is a PK with auto increment, truncate will reset the counter

• DELETE 和 TRUNCATE 与 TRANSACTION 一起使用时都可以回滚（TRUNCATE 在 SQL Server 中可以回滚，但在 MySQL 中不能）。
• 如果有一个自动递增的 PK，truncate 将重置计数器

http://beginner-sql-tutorial.com/sql-delete-statement.htm

http://beginner-sql-tutorial.com/sql-delete-statement.htm

回答by naveen goyal

Difference

区别

The most important difference is DELETE operations are transaction-safe and logged, which means DELETE can be rolled back. TRUNCATE cannot be done inside a transaction and can't be rolled back. Because TRUNCATE is not logged recovering a mistakenly TRUNCATEd table is a much bigger problem than recovering from a DELETE.

最重要的区别是 DELETE 操作是事务安全和日志记录的，这意味着 DELETE 可以回滚。TRUNCATE 不能在事务内完成，也不能回滚。因为 TRUNCATE 没有被记录，恢复错误的 TRUNCATE 表比从 DELETE 恢复要大得多的问题。

DELETE will fail if foreign key constraints are broken; TRUNCATE may not honor foreign key constraints (it does for InnoDB tables). DELETE will fire any ON DELETE triggers; TRUNCATE will not.

如果外键约束被破坏，DELETE 将失败；TRUNCATE 可能不遵守外键约束（它适用于 InnoDB 表）。DELETE 将触发任何 ON DELETE 触发器；TRUNCATE 不会。

FASTER

快点

Truncate operations drop and re-create the table, which is much faster than deleting rows one by one, particularly for large tables.

截断操作删除并重新创建表，这比逐行删除要快得多，特别是对于大表。

Where to USE

在哪里使用

truncate

截短

when table set to empty, and need reset auto-incrementing keys to 1. It's faster than DELETE because it deletes all data. DELETE will scan the table to generate a count of rows that were affected.

当表设置为空时，需要将自动递增键重置为 1。它比 DELETE 快，因为它删除所有数据。DELETE 将扫描表以生成受影响的行数。

delete

删除

need rows to delete based on an optional WHERE clause. need logs and apply foreign key constraints

需要根据可选的 WHERE 子句删除行。需要日志并应用外键约束

回答by Lingasamy Sakthivel

The DELETEcommand is used to remove rows from a table

该DELETE命令用于从表中删除行

TRUNCATEremoves all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUNCATEis faster and doesn't use as much undo space as a DELETE.

TRUNCATE从表中删除所有行。该操作无法回滚，也不会触发任何触发器。因此，TRUNCATE速度更快，并且不会像DELETE.

回答by Suresh Kamrushi

Difference:

区别：

1) Truncate deletes the complete data from the table and next auto increment id will start with 1 whereas delete will start with next id.
2) Both will keep structure intact and delete data only.
3) with delete you can use limit whereas with truncate you can't.

1) 截断从表中删除完整数据，下一个自增 id 将从 1 开始，而 delete 将从下一个 id 开始。
2) 两者都将保持结构完整并仅删除数据。
3）使用 delete 可以使用 limit 而使用 truncate 则不能。