SQL中TRUNCATE和DELETE的区别是什么?
如果你的答案是针对特定平台的,请注明。
SQL中TRUNCATE和DELETE的区别是什么?
如果你的答案是针对特定平台的,请注明。
当前回答
都是很好的答案,我必须补充一句:
由于TRUNCATE TABLE是一个DDL(数据定义语言)命令,而不是DML(数据操作语言)命令,删除触发器不会运行。
其他回答
对于SQL Server或MySQL,如果有一个带有自动递增的PK, truncate将重置计数器。
DELETE语句可以有一个WHERE子句来删除特定的记录,而TRUNCATE语句不需要任何子句并擦除整个表。 重要的是,DELETE语句记录删除日期,而TRUNCATE语句不记录删除日期。
简而言之,truncate不会记录任何内容(因此更快,但不能撤消),而delete会记录日志(并且可以是更大事务的一部分,将回滚等)。如果您在开发中有不想在表中使用的数据,通常最好截断,因为您不会冒填满事务日志的风险
微软sql server的另一个不同之处在于,使用delete,你可以使用output语句来跟踪哪些记录被删除了,例如:
delete from [SomeTable]
output deleted.Id, deleted.Name
你不能用truncate这样做。
DROP The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back. TRUNCATE TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUNCATE is faster and doesn't use as much undo space as a DELETE. Table level lock will be added when Truncating. DELETE The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire. Row level lock will be added when deleting.
来自:http://www.orafaq.com/faq/difference_between_truncate_delete_and_drop_commands