SQL中TRUNCATE和DELETE的区别是什么?

如果你的答案是针对特定平台的,请注明。


当前回答

Truncate命令用于重新初始化表,它是一个删除表中所有行的DDL命令。DELETE是一个DML命令,用于根据某些条件删除一行或一组行,如果没有指定condition,则该命令将删除表中的所有行。

其他回答

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. TRUNCATE TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE. 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. DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.

来自:http://www.orafaq.com/faq/difference_between_truncate_delete_and_drop_commands

Truncate命令用于重新初始化表,它是一个删除表中所有行的DDL命令。DELETE是一个DML命令,用于根据某些条件删除一行或一组行,如果没有指定condition,则该命令将删除表中的所有行。

Truncate and Delete in SQL are two commands which is used to remove or delete data from table. Though quite basic in nature both Sql commands can create lot of trouble until you are familiar with details before using it. An Incorrect choice of command can result is either very slow process or can even blew up log segment, if too much data needs to be removed and log segment is not enough. That's why it's critical to know when to use truncate and delete command in SQL but before using these you should be aware of the Differences between Truncate and Delete, and based upon them, we should be able to find out when DELETE is better option for removing data or TRUNCATE should be used to purge tables.

参考资料请点击这里

一眨眼的功夫就不能做DDL。

是的,DELETE比较慢,TRUNCATE比较快。为什么?

DELETE必须读取记录,检查约束,更新块,更新索引,并生成重做/撤销。所有这些都需要时间。

TRUNCATE只是在数据库中为表(High Water Mark)调整一个指针,然后!数据不见了。

这是Oracle特有的,AFAIK。