SQL 中的 TRUNCATE 和 DELETE 有什么区别

What's the difference between TRUNCATE and DELETE in SQL

提问by David Aldridge

What's the difference between TRUNCATEand DELETEin SQL?

SQL 中的TRUNCATE和 有什么区别DELETE？

If your answer is platform specific, please indicate that.

如果您的答案是特定于平台的，请指出。

采纳答案by David Aldridge

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.

这是差异列表。我已经强调了 Oracle 特定的特性，希望社区也可以添加其他供应商的特定差异。大多数供应商常见的差异可以直接放在标题下方，差异在下方突出显示。

---

---

General Overview

总体概述

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.

如果您想快速删除表中的所有行，并且您确实确定要这样做，并且您没有针对表的外键，那么 TRUNCATE 可能会比 DELETE 更快.

Various system-specific issues have to be considered, as detailed below.

必须考虑各种特定于系统的问题，详情如下。

---

---

Statement type

语句类型

Delete is DML, Truncate is DDL (What is DDL and DML?)

Delete 是 DML，Truncate 是 DDL（什么是 DDL 和 DML？）

---

---

Commit and Rollback

提交和回滚

Variable by vendor

因供应商而异

SQL*Server

SQL*服务器

Truncate can be rolled back.

截断可以回滚。

PostgreSQL

PostgreSQL

Truncate can be rolled back.

截断可以回滚。

Oracle

甲骨文

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.

因为 TRUNCATE 是 DDL，所以它涉及两次提交，一次在语句执行之前，一次在语句执行之后。因此截断不能回滚，截断过程中的失败无论如何都会发出提交。

However, see Flashback below.

但是，请参阅下面的闪回。

---

---

Space reclamation

空间开垦

Delete does not recover space, Truncate recovers space

删除不恢复空间，截断恢复空间

Oracle

甲骨文

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.

如果您使用 REUSE STORAGE 子句，则不会取消分配数据段，如果要使用数据重新加载表，这会稍微提高效率。高水位标记被重置。

---

---

Row scope

行范围

Delete can be used to remove all rows or only a subset of rows. Truncate removes all rows.

删除可用于删除所有行或仅删除行的子集。截断删除所有行。

Oracle

甲骨文

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.

对表进行分区时，可以单独截断各个分区，因此可以部分删除表的所有数据。

---

---

Object types

对象类型

Delete can be applied to tables and tables inside a cluster. Truncate applies only to tables or the entire cluster. (May be Oracle specific)

删除可以应用于表和集群内的表。截断仅适用于表或整个集群。（可能是特定于 Oracle 的）

---

---

Data Object Identity

数据对象标识

Oracle

甲骨文

Delete does not affect the data object id, but truncate assigns a new data object id unlessthere 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.

删除不会影响数据对象 id，但 truncate 会分配一个新的数据对象 id，除非自创建以来从未对表进行过插入即使是回滚的单个插入也会导致在截断时分配新的数据对象 id .

---

---

Flashback (Oracle)

闪回 (Oracle)

Flashback works across deletes, but a truncate prevents flashback to states prior to the operation.

闪回跨删除工作，但截断可防止闪回到操作之前的状态。

However, from 11gR2 the FLASHBACK ARCHIVE feature allows this, except in Express Edition

但是，从 11gR2 开始，FLASHBACK ARCHIVE 功能允许这样做，Express Edition 除外

Use of FLASHBACK in Oraclehttp://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#ADFNS638

在 Oracle 中使用 FLASHBACK http://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#ADFNS638

---

---

Privileges

特权

Variable

多变的

Oracle

甲骨文

Delete can be granted on a table to another user or role, but truncate cannot be without using a DROP ANY TABLE grant.

可以将表上的删除授予另一个用户或角色，但不能在不使用 DROP ANY TABLE 授予的情况下进行截断。

---

---

Redo/Undo

重做/撤销

Delete generates a small amount of redo and a large amount of undo. Truncate generates a negligible amount of each.

删除会生成少量重做和大量撤消。Truncate 生成的每个值都可以忽略不计。

---

---

Indexes

索引

Oracle

甲骨文

A truncate operation renders unusable indexes usable again. Delete does not.

截断操作使不可用的索引再次可用。删除没有。

---

---

Foreign Keys

外键

A truncate cannot be applied when an enabled foreign key references the table. Treatment with delete depends on the configuration of the foreign keys.

当启用的外键引用表时，不能应用截断。删除处理取决于外键的配置。

---

---

Table Locking

表锁定

Oracle

甲骨文

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.

截断需要独占表锁，删除需要共享表锁。因此，禁用表锁是一种防止对表进行截断操作的方法。

---

---

Triggers

触发器

DML triggers do not fire on a truncate.

DML 触发器不会在截断时触发。

Oracle

甲骨文

DDL triggers are available.

DDL 触发器可用。

---

---

Remote Execution

远程执行

Oracle

甲骨文

Truncate cannot be issued over a database link.

不能通过数据库链接发出截断。

---

---

Identity Columns

标识列

SQL*Server

SQL*服务器

Truncate resets the sequence for IDENTITY column types, delete does not.

截断重置 IDENTITY 列类型的序列，删除不会。

---

---

Result set

结果集

In most implementations, a DELETEstatement can return to the client the rows that were deleted.

在大多数实现中，DELETE语句可以将被删除的行返回给客户端。

e.g. in an Oracle PL/SQL subprogram you could:

例如，在 Oracle PL/SQL 子程序中，您可以：

DELETE FROM employees_temp
WHERE       employee_id = 299 
RETURNING   first_name,
            last_name
INTO        emp_first_name,
            emp_last_name;

回答by Bhaumik Patel

The difference between truncate and delete is listed below:

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)              |
+----------------------------------------+----------------------------------------------+

回答by Mohit Singh

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.

降低

DROP 命令从数据库中删除一个表。所有表的行、索引和权限也将被删除。不会触发 DML 触发器。该操作无法回滚。

截短

TRUNCATE 从表中删除所有行。该操作无法回滚，也不会触发任何触发器。因此，TRUNCATE 更快，并且不会像 DELETE 那样使​​用那么多的撤消空间。截断时将添加表级锁。

删除

DELETE 命令用于从表中删除行。WHERE 子句可用于仅删除某些行。如果未指定 WHERE 条件，则将删除所有行。执行 DELETE 操作后，您需要提交或回滚事务以使更改永久或撤消。请注意，此操作将触发表上的所有 DELETE 触发器。删除时会加行级锁。

From: http://www.orafaq.com/faq/difference_between_truncate_delete_and_drop_commands

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

回答by polara

All good answers, to which I must add:

所有好的答案，我必须补充：

Since TRUNCATE TABLEis a DDL (Data Defination Language), not a DML (Data Manipulation Langauge) command, the Delete Triggersdo not run.

由于TRUNCATE TABLE是 DDL（数据定义语言），而不是 DML（数据操作语言）命令，所以Delete Triggers不要运行。

回答by Shamseer K

Summary of Delete Vs Truncate in SQL server
For Complete Article follow this link : http://codaffection.com/sql-server-article/delete-vs-truncate-in-sql-server/

SQL server 中删除与截断的总结
完整文章请点击此链接：http: //codaffection.com/sql-server-article/delete-vs-truncate-in-sql-server/

Taken from dotnet mob article :Delete Vs Truncate in SQL Server

摘自 dotnet mob 文章：在 SQL Server 中删除与截断

回答by mathieu

With SQL Server or MySQL, if there is a PK with auto increment, truncate will reset the counter.

对于 SQL Server 或 MySQL，如果有自动递增的 PK，truncate 将重置计数器。

回答by Walter Mitty

"Truncate doesn't log anything" is correct. I'd go further:

“截断不记录任何内容”是正确的。我会更进一步：

Truncate is not executed in the context of a transaction.

截断不在事务的上下文中执行。

The speed advantage of truncate over delete should be obvious. That advantage ranges from trivial to enormous, depending on your situation.

truncate 相对于 delete 的速度优势应该是显而易见的。根据您的情况，这种优势从微不足道到巨大。

However, I've seen truncate unintentionally break referential integrity, and violate other constraints. The power that you gain by modifying data outside a transaction has to be balanced against the responsibility that you inherit when you walk the tightrope without a net.

但是，我已经看到 truncate 无意中破坏了参照完整性，并违反了其他约束。您通过在事务外修改数据而获得的权力必须与您在没有网络的情况下走钢丝时所继承的责任相平衡。

回答by Sachin Chourasiya

TRUNCATEis the DDL statement whereas DELETEis a DML statement. Below are the differences between the two:

TRUNCATE是 DDL 语句，而DELETE是 DML 语句。下面是两者的区别：

1. As TRUNCATEis a DDL (Data definition language) statement it does not require a commit to make the changes permanent. And this is the reason why rows deleted by truncate could not be rollbacked. On the other hand DELETEis a DML (Data manipulation language) statement hence requires explicit commit to make its effect permanent.

2. TRUNCATEalways removes all the rows from a table, leaving the table empty and the table structure intact whereas DELETEmay remove conditionally if the where clause is used.

3. The rows deleted by TRUNCATE TABLEstatement cannot be restored and you can not specify the where clause in the TRUNCATEstatement.

4. TRUNCATEstatements does not fire triggers as opposed of on delete trigger on DELETEstatement

1. 作为TRUNCATEDDL（数据定义语言）语句，它不需要提交即可使更改永久化。这就是无法回滚被 truncate 删除的行的原因。另一方面DELETE是 DML（数据操作语言）语句，因此需要显式提交以使其效果永久。

2. TRUNCATE总是从表中删除所有行，使表为空，表结构保持不变，而DELETE如果使用 where 子句，则可能有条件地删除。

3. TRUNCATE TABLE语句删除的行不能恢复，不能在语句中指定where子句TRUNCATE。

4. TRUNCATE语句不会触发触发器，而不是DELETE语句上的删除触发器

Hereis the very good link relevant to the topic.

这是与该主题相关的非常好的链接。

回答by Purvi Barot

TRUNCATE

截短

TRUNCATE SQL query removes all rows from a table, without logging the individual row deletions.

TRUNCATE SQL 查询从表中删除所有行，而不记录单个行删除。

• TRUNCATE is a DDL command.
• TRUNCATE is executed using a table lock and whole table is locked for remove all records.
• We cannot use WHERE clause with TRUNCATE.
• TRUNCATE removes all rows from a table.
• Minimal logging in transaction log, so it is faster performance wise.
• TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.
• To use Truncate on a table you need at least ALTER permission on the table.
• Truncate uses less transaction space than the Delete statement.
• Truncate cannot be used with indexed views.
• TRUNCATE is faster than DELETE.

• TRUNCATE 是一个 DDL 命令。
• TRUNCATE 使用表锁执行，并锁定整个表以删除所有记录。
• 我们不能将 WHERE 子句与 TRUNCATE 一起使用。
• TRUNCATE 从表中删除所有行。
• 事务日志中的日志记录最少，因此性能更快。
• TRUNCATE TABLE 通过取消分配用于存储表数据的数据页来删除数据，并仅在事务日志中记录页取消分配。
• 要在表上使用 Truncate，您至少需要对该表具有 ALTER 权限。
• Truncate 使用的事务空间比 Delete 语句少。
• 截断不能与索引视图一起使用。
• TRUNCATE 比 DELETE 快。

DELETE

删除

To execute a DELETE queue, delete permissions are required on the target table. If you need to use a WHERE clause in a DELETE, select permissions are required as well.

要执行 DELETE 队列，需要对目标表具有删除权限。如果您需要在 DELETE 中使用 WHERE 子句，则还需要选择权限。

• DELETE is a DML command.
• DELETE is executed using a row lock, each row in the table is locked for deletion.
• We can use where clause with DELETE to filter & delete specific records.
• The DELETE command is used to remove rows from a table based on WHERE condition.
• It maintain the log, so it slower than TRUNCATE.
• The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row.
• Identity of column keep DELETE retains the identity.
• To use Delete you need DELETE permission on the table.
• Delete uses the more transaction space than Truncate statement.
• Delete can be used with indexed views.

• DELETE 是 DML 命令。
• DELETE 使用行锁执行，表中的每一行都被锁定以进行删除。
• 我们可以使用 where 子句和 DELETE 来过滤和删除特定的记录。
• DELETE 命令用于根据 WHERE 条件从表中删除行。
• 它维护日志，所以它比 TRUNCATE 慢。
• DELETE 语句一次删除一行，并在事务日志中为每个删除的行记录一个条目。
• 列的标识保留 DELETE 保留标识。
• 要使用删除，您需要对表具有 DELETE 权限。
• Delete 比 Truncate 语句使用更多的事务空间。
• 删除可以与索引视图一起使用。

回答by DCookie

Yes, DELETE is slower, TRUNCATE is faster. Why?

是的，DELETE 较慢，TRUNCATE 较快。为什么？

DELETE must read the records, check constraints, update the block, update indexes, and generate redo/undo. All of that takes time.

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

TRUNCATE simply adjusts a pointer in the database for the table (the High Water Mark) and poof! the data is gone.

TRUNCATE 只是在数据库中为表（高水位线）调整一个指针，噗！数据没了。

This is Oracle specific, AFAIK.

这是特定于 Oracle 的，AFAIK。