在SQL中，TRUNCATE和DELETE有什么区别？

Question

在SQL中，TRUNCATE和DELETE有什么区别？

sqldatabasetruncate

386

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

如果你的回答与平台相关，请注明。

- David Aldridge

7

所有答案都是特定于平台的。标准 SQL 中没有 TRUNCATE 命令。因此，它是一种专有功能，并且对于每个 DBMS 供应商来说意义不同。 - nvogel

答案非常具体化，因为正如sqlvogel所指出的那样，这是一个非标准命令（TRUNCATE）。要么将其标记为“oracle”，要么让我们将其作为社区维基风格的答案，并列出每个主要关系数据库管理系统（Oracle、MS-MSQL、PostgreSQL都实现了TRUNCATE）的后果。 - reedstrm

如果交易已经完成，也就是已提交(COMMITTED)，那么我们无法回滚(TRUNCATE)命令，但是我们仍然可以从日志文件中回滚(ROLLBACK)DELETE命令，因为在需要从日志文件中回滚时，DELETE会将记录写入日志文件。 - user4768611

32个回答

230

下面列出了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)              |
+----------------------------------------+----------------------------------------------+

- Bhaumik Patel

截断表的重置标识是什么意思？但是，删除呢？ - Ravi

2

@jWeaver：这意味着当您将Identity Specification属性设置为Primary key字段的True时，当您向该表插入数据时，主键列保持像1,2,3,4,5...（如果Identity从1开始且种子为1）的值，当您截断表时，它将失去所有身份值，因此当您再次开始向该表插入数据时，它将从1开始而不是上次离开的地方。在DELETE中，情况相反，即使您执行DELETE语句，它也会保留身份值。对于上图中DELETE列中第二个比较点错误，我感到抱歉。 - Bhaumik Patel

似乎你正在回答关于SQL SERVER的问题。 - Ravi

5

在SQL SERVER中，TRUNCATE和DELETE均可回滚。在第二行中，DELETE不会重置标识符。现在你该如何编辑这篇帖子呢？这就是在StackOverflow中使用图片的一个糟糕之处。 - Mahmood Jenami

2

截断操作正在回滚！（SQL SERVER） - Aimal Khan

70

DROP

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

TRUNCATE

TRUNCATE从表中删除所有行。此操作无法回滚，也不会触发任何触发器。因此，TRUNCATE速度更快，使用的撤消空间也比DELETE少。在截断时，将添加表级锁。

DELETE

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

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

- Mohit Singh

25

所有的答案都很好，我需要补充一点：

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

- polara

啊，触发器...这是个好主意。我会把它加到我列出的清单上，并且如果可以的话，会给你 Polara 认可。 - David Aldridge

SQL Server不允许您截断带有外键的表，因此您的级联点可能会失效，具体取决于平台。 - Meff

PostgreSQL有一个“TRUNCATE触发器”。 - user330315

24

SQL Server中删除（Delete）和截断（Truncate）的概述

完整文章请访问：http://codaffection.com/sql-server-article/delete-vs-truncate-in-sql-server/

摘自dotnet mob文章：SQL Server中的Delete与Truncate

- Shamseer K

20

使用SQL Server或MySQL，如果有一个带自增的主键(PK)，那么截断(truncate)操作将会重置该计数器。

- mathieu

澄清一下，这是针对 SQL Server 的，如果表中有一个列被定义为 IDENTITY。删除操作将保留最后一个自动分配的 ID，而截断操作则会重置计数器。 - Codewerks

由于问题标记为ORACLE，因此此答案是错误的，因此被投票否决。 - Guy

哎呀，没看到 Oracle 标签 :) - mathieu

+1 是真的，并将其重置为 0。如果您想改为 1：DBCC CHECKIDENT (table_name，RESEED，1)。 - JohnB

我已将此添加到社区答案中，并使其更加友好，以记录特定于供应商的问题。 - David Aldridge

12

"Truncate不会记录任何信息"是正确的。我会进一步说明：

在事务上下文中不会执行truncate。

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

然而，我曾经看到过truncate无意中破坏了引用完整性并违反了其他约束条件。在没有事务支持的情况下修改数据所获得的权力必须与在没有保障的情况下行走钢丝时承担的责任相平衡。

- Walter Mitty

11

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

TRUNCATE是DDL（数据定义语言）语句，因此不需要提交以使更改永久生效。这就是为什么通过truncate删除的行无法回滚的原因。另一方面，DELETE是DML（数据操作语言）语句，因此需要显式提交以使其效果永久。
TRUNCATE总是从表中删除所有行，使表为空，表结构保持不变，而DELETE可以根据使用where子句来有条件地删除。
通过TRUNCATE TABLE语句删除的行不能恢复，并且在TRUNCATE语句中不能指定where子句。
TRUNCATE语句不会触发触发器，而DELETE语句上的on delete触发器则相反。

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

- Sachin Chourasiya

8

是的，DELETE比TRUNCATE慢。为什么呢？

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

TRUNCATE只需调整表在数据库中的指针（高水位线），数据就会消失。

据我所知，这只适用于Oracle。

- DCookie

PostgreSQL 也是类似的。 - Gavin M. Roy

6

如果你不小心使用Delete/Truncate从表中删除了所有数据。你可以回滚提交的事务。恢复最后一个备份并运行事务日志，直到Delete/Truncate即将发生。

以下相关信息来自博客文章：

在处理数据库时，我们使用Delete和Truncate而不知道它们之间的区别。在本文中，我们将讨论SQL中Delete和Truncate的区别。

Delete： - Delete是DML命令。 - 通过行锁执行Delete语句，表中的每一行都需要进行锁定以便删除。 - 我们可以在Where子句中指定过滤器。 - 如果Where条件存在，则删除指定的数据。 - 因为操作被单独记录，所以Delete会激活触发器。 - 比Truncate慢，因为它会保留日志。

Truncate： - Truncate是DDL命令。 - Truncate表始终锁定表和页面，但不锁定每一行。因为它移除了所有数据。 - 不能使用Where条件。 - 它删除所有的数据。 - Truncate表无法激活触发器，因为该操作不记录单个行删除。 - 性能更快，因为它不会保留任何日志。

注意：使用Transaction时，Delete和Truncate都可以回滚。如果事务已完成，则无法回滚Truncate命令，但是我们仍然可以从日志文件中回滚Delete命令，因为Delete在需要将其从日志文件中回滚时会将记录写入日志文件。

如果您有一个引用到您尝试截断的表的Foreign key约束，即使引用表中没有数据，这也无法工作。这是因为外键检查是使用DDL而不是DML进行的。可以通过暂时禁用与表相关的外键约束来解决此问题。

Delete table是一项记录操作。因此，删除每行时都会将其记录在事务日志中，这使其变慢。 Truncate table也会删除表中的所有行，但它不会记录每行的删除，而是记录表的数据页面的取消分配，这使其更快。

~ 如果你不小心使用Delete/Truncate从表中删除了所有数据。你可以回滚提交的事务。恢复最后一个备份并运行事务日志，直到Delete/Truncate即将发生。

- wpzone4u

1

感谢@Lucas：他将内容内联。我只是将其放在块引用中，以使它明显来自不同的来源。 - AstroCB

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- David Aldridge · Accepted Answer

以下是一些不同之处。我突出了 Oracle 特定的功能，希望社区可以添加其他供应商的特定差异。对于大多数供应商通用的差异，可以直接放在标题下面，差异则在下面突出。

总体概述

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

必须考虑各种系统特定问题，如下所述。

语句类型

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

提交和回滚

根据供应商而变化

SQL*Server

Truncate 可以回滚。

PostgreSQL

Truncate 可以回滚。

Oracle

由于 TRUNCATE 是 DDL，它涉及两个提交，一个在语句执行之前，一个在之后。因此，TRUNCATE 无法回滚，并且在 TRUNCATE 过程中失败将已经发出提交。

但是，请参见下面的 Flashback。

空间回收

Delete 不会恢复空间，Truncate 会恢复空间。

Oracle

如果使用 REUSE STORAGE 子句，则数据段不会被取消分配，如果要重新加载数据，则可能稍微更高效。高水位线被重置。

行范围

Delete 可用于删除所有行或仅子集行。Truncate 删除所有行。

Oracle

当表被分区时，各个分区可以被独立地截断，因此可以部分删除整个表的数据。

对象类型

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

数据对象标识

Oracle

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

闪回（Oracle）

闪回适用于删除操作，但截断会阻止返回到操作之前的状态。

但是，从11gR2开始，FLASHBACK ARCHIVE功能允许执行此操作，除了Express Edition。

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

特权

可变量

Oracle

可以将删除授权给另一个用户或角色，但是截断不能，除非使用DROP ANY TABLE授予。

重做/撤消

Delete生成少量重做和大量撤销。截断生成微不足道的每个数量。

索引

Oracle

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

外键
如果启用了外键引用该表，则无法使用截取（truncate）操作，需要根据外键的配置来决定是否使用删除（delete）操作。

表锁定

Oracle

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

触发器

截取（truncate）操作不会触发DML触发器。

Oracle

DDL触发器可用。

远程执行

Oracle

无法通过数据库链接进行截取（truncate）操作。

自增列

SQL*Server

截取（truncate）操作将重置IDENTITY列类型的序列，而删除（delete）操作不会。

结果集

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

例如，在Oracle PL/SQL子程序中：
DELETE FROM employees_temp WHERE employee_id = 299 RETURNING first_name, last_name INTO emp_first_name, emp_last_name;