MySQL:截断表与从表中删除 [英] MySQL: Truncate Table vs Delete From Table

问题描述

什么时候使用DELETE命令而不是TRUNCATE命令?我试图在Internet上找到，但是两个命令都删除了数据；我无法分辨出差异.

When do we use the DELETE command versus the TRUNCATE command? I am trying to find on the Internet but both commands delete the data; I can't tell the difference.

推荐答案

从表中删除

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.
8. LIMIT clause can also be used to set a limit on the number of rows to be deleted.
9. ORDER BY clause can be used in DELETE statement. In this case, the rows are deleted in the specified order.

截断表

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 reset the auto increment number.
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.
8. Cannot use LIMIT and ORDER BY.

与TRANSACTION一起使用时，可以回滚

Delete 和 TRUNCATE . 如果有一个具有自动递增功能的主键，则truncate将重置计数器.

DELETE and TRUNCATE both can be rolled back when used with TRANSACTION. If there is a primary key with auto increment, truncate will reset the counter.

这篇关于MySQL:截断表与从表中删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！