SQL中TRUNCATE和DELETE的区别是什么?
如果你的答案是针对特定平台的,请注明。
SQL中TRUNCATE和DELETE的区别是什么?
如果你的答案是针对特定平台的,请注明。
当前回答
对于SQL Server或MySQL,如果有一个带有自动递增的PK, truncate将重置计数器。
其他回答
truncate和delete的区别如下:
+----------------------------------------+----------------------------------------------+
| Truncate | Delete |
+----------------------------------------+----------------------------------------------+
| We can't Rollback after performing | We can Rollback after delete. |
| Truncate. | |
| | |
| Example: | Example: |
| BEGIN TRAN | BEGIN TRAN |
| TRUNCATE TABLE tranTest | DELETE FROM tranTest |
| SELECT * FROM tranTest | SELECT * FROM tranTest |
| ROLLBACK | ROLLBACK |
| SELECT * FROM tranTest | SELECT * FROM tranTest |
+----------------------------------------+----------------------------------------------+
| Truncate reset identity of table. | Delete does not reset identity of table. |
+----------------------------------------+----------------------------------------------+
| It locks the entire table. | It locks the table row. |
+----------------------------------------+----------------------------------------------+
| Its DDL(Data Definition Language) | Its DML(Data Manipulation Language) |
| command. | command. |
+----------------------------------------+----------------------------------------------+
| We can't use WHERE clause with it. | We can use WHERE to filter data to delete. |
+----------------------------------------+----------------------------------------------+
| Trigger is not fired while truncate. | Trigger is fired. |
+----------------------------------------+----------------------------------------------+
| Syntax : | Syntax : |
| 1) TRUNCATE TABLE table_name | 1) DELETE FROM table_name |
| | 2) DELETE FROM table_name WHERE |
| | example_column_id IN (1,2,3) |
+----------------------------------------+----------------------------------------------+
是的,DELETE比较慢,TRUNCATE比较快。为什么?
DELETE必须读取记录,检查约束,更新块,更新索引,并生成重做/撤销。所有这些都需要时间。
TRUNCATE只是在数据库中为表(High Water Mark)调整一个指针,然后!数据不见了。
这是Oracle特有的,AFAIK。
如果将TRUNCATE包装在事务中,则可以回滚。
请参阅下面的两个参考资料并自行测试:-
http://blog.sqlauthority.com/2007/12/26/sql-server-truncate-cant-be-rolled-back-using-log-files-after-transaction-session-is-closed/
http://sqlblog.com/blogs/kalen_delaney/archive/2010/10/12/tsql-tuesday-11-rolling-back-truncate-table.aspx
截断与删除是SQL面试中最臭名昭著的问题之一。一定要向面试官解释清楚,否则可能会让你丢掉这份工作。问题是没有多少人意识到这一点,所以如果你告诉他们YES Truncate可以回滚,他们很可能会认为答案是错误的。
最大的区别是truncate是不记录日志的操作,而delete是。
简单地说,这意味着在数据库崩溃的情况下,不能通过截断恢复所操作的数据,但可以通过删除恢复。
详情请点击这里
对于SQL Server或MySQL,如果有一个带有自动递增的PK, truncate将重置计数器。