MySQL：Truncate Table与Delete From Table的区别

Question

我们什么时候使用DELETE命令，什么时候使用TRUNCATE命令？我在网上查找但是这两个命令都可以删除数据，我无法区分它们的不同。

- Adnan Siddique

---

截断命令比删除命令更快。如果您想要删除整个表，则最好使用截断命令。 - Naved Munshi

请将以下与编程有关的内容从英语翻译成中文。仅返回翻译文本：可以参考以下链接：https://dev59.com/8mIj5IYBdhLWcg3wKiLs - Amy Roy

https://dev59.com/Ck3Sa4cB1Zd3GeqPwJP5#2763332 - Shantanu Gupta

你真的问过当你完全清空表格和删除其中某些记录时有什么区别吗？这是“常识”，要根据情况选择使用哪种方法。 - N.B.

Answer 1

从表中删除数据

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.

TRUNCATE TABLE

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.

DELETE 和 TRUNCATE 在使用 TRANSACTION 时都可以回滚。 如果有一个带有自增的主键，TRUNCATE 将重置计数器。

- Saharsh Shah