SQL 中的 TRUNCATE 和 DELETE 有什么区别 [英] What's the difference between TRUNCATE and DELETE in SQL
问题描述
SQL中TRUNCATE和DELETE有什么区别?
What's the difference between TRUNCATE and DELETE in SQL?
如果您的回答是针对特定平台的，请指出.
If your answer is platform specific, please indicate that.
推荐答案
以下是差异列表.我已经强调了 Oracle 特定的特性，希望社区也可以添加其他供应商的特定差异.大多数供应商共有的差异可以直接放在标题下方，差异在下方突出显示.
Here's a list of differences. I've highlighted Oracle-specific features, and hopefully the community can add in other vendors' specific difference also. Differences that are common to most vendors can go directly below the headings, with differences highlighted below.
如果您想快速删除表中的所有行，并且您确实确定要这样做，并且您没有针对表的外键，那么 TRUNCATE 可能会更快而不是 DELETE.
If you want to quickly delete all of the rows from a table, and you're really sure that you want to do it, and you do not have foreign keys against the tables, then a TRUNCATE is probably going to be faster than a DELETE.
必须考虑各种特定于系统的问题，详情如下.
Various system-specific issues have to be considered, as detailed below.
删除是 DML，截断是 DDL(什么是 DDL 和 DML?)
Delete is DML, Truncate is DDL (What is DDL and DML?)
因供应商而异
SQL*Server
截断可以回滚.
PostgreSQL
截断可以回滚.
甲骨文
因为 TRUNCATE 是 DDL，所以它涉及两次提交，一次在语句执行之前，一次在语句执行之后.因此截断不能回滚，截断过程中的失败无论如何都会发出提交.
Because a TRUNCATE is DDL it involves two commits, one before and one after the statement execution. Truncate can therefore not be rolled back, and a failure in the truncate process will have issued a commit anyway.
但是，请参阅下面的闪回.
However, see Flashback below.
删除不回收空间，截断回收空间
Delete does not recover space, Truncate recovers space
甲骨文
如果您使用 REUSE STORAGE 子句，则不会取消分配数据段，如果要使用数据重新加载表，这会稍微提高效率.高水位标记被重置.
If you use the REUSE STORAGE clause then the data segments are not de-allocated, which can be marginally more efficient if the table is to be reloaded with data. The high water mark is reset.
Delete 可用于删除所有行或仅删除行的子集.截断删除所有行.
Delete can be used to remove all rows or only a subset of rows. Truncate removes all rows.
甲骨文
对表进行分区时，可以单独截断各个分区，因此可以部分删除表的所有数据.
When a table is partitioned, the individual partitions can be truncated in isolation, thus a partial removal of all the table's data is possible.
删除可以应用于表和集群内的表.截断仅适用于表或整个集群.(可能是特定于 Oracle 的)
Delete can be applied to tables and tables inside a cluster. Truncate applies only to tables or the entire cluster. (May be Oracle specific)
甲骨文
删除不影响数据对象id，但truncate分配一个新的数据对象id除非自创建以来从未对表进行插入即使是回滚的单个插入也会导致截断时要分配的新数据对象 ID.
Delete does not affect the data object id, but truncate assigns a new data object id unless there has never been an insert against the table since its creation Even a single insert that is rolled back will cause a new data object id to be assigned upon truncation.
闪回可跨删除工作，但截断可防止闪回到操作之前的状态.
Flashback works across deletes, but a truncate prevents flashback to states prior to the operation.
但是，从 11gR2 开始，FLASHBACK ARCHIVE 功能允许这样做，Express Edition 除外
However, from 11gR2 the FLASHBACK ARCHIVE feature allows this, except in Express Edition
FLASHBACK 在 Oracle 中的使用http://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#ADFNS638
变量
甲骨文
可以将表上的删除权限授予另一个用户或角色，但不能在不使用 DROP ANY TABLE 权限的情况下进行截断.
Delete can be granted on a table to another user or role, but truncate cannot be without using a DROP ANY TABLE grant.
删除会产生少量的重做和大量的撤销.Truncate 生成的每个值都可以忽略不计.
Delete generates a small amount of redo and a large amount of undo. Truncate generates a negligible amount of each.
甲骨文
截断操作使不可用的索引再次可用.删除没有.
A truncate operation renders unusable indexes usable again. Delete does not.
当启用的外键引用表时，不能应用截断.删除处理取决于外键的配置.
A truncate cannot be applied when an enabled foreign key references the table. Treatment with delete depends on the configuration of the foreign keys.
甲骨文
Truncate 需要一个排它表锁，delete 需要一个共享表锁.因此，禁用表锁是防止对表进行截断操作的一种方式.
Truncate requires an exclusive table lock, delete requires a shared table lock. Hence disabling table locks is a way of preventing truncate operations on a table.
DML 触发器不会在截断时触发.
DML triggers do not fire on a truncate.
甲骨文
DDL 触发器可用.
甲骨文
不能通过数据库链接发出截断.
Truncate cannot be issued over a database link.
SQL*Server
截断重置 IDENTITY 列类型的序列，删除不会.
Truncate resets the sequence for IDENTITY column types, delete does not.
在大多数实现中，DELETE 语句可以将被删除的行返回给客户端.
In most implementations, a DELETE statement can return to the client the rows that were deleted.
例如在 Oracle PL/SQL 子程序中，您可以:
e.g. in an Oracle PL/SQL subprogram you could:
DELETE FROM employees_temp
WHERE employee_id = 299
RETURNING first_name,
last_name
INTO emp_first_name,
emp_last_name;
这篇关于SQL 中的 TRUNCATE 和 DELETE 有什么区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
