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

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


当前回答

TRUNCATE是DDL语句,而DELETE是DML语句。以下是两者的区别:

As TRUNCATE is 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 DELETE is a DML (Data manipulation language) statement hence requires explicit commit to make its effect permanent. TRUNCATE always removes all the rows from a table, leaving the table empty and the table structure intact whereas DELETE may remove conditionally if the where clause is used. The rows deleted by TRUNCATE TABLE statement cannot be restored and you can not specify the where clause in the TRUNCATE statement. TRUNCATE statements does not fire triggers as opposed of on delete trigger on DELETE statement

这里有一个非常好的与主题相关的链接。

其他回答

对原始答案的一个小修正——删除也会产生大量的重做(因为undo本身是由重做保护的)。这可以从autotrace输出中看到:

SQL> delete from t1;

10918 rows deleted.

Elapsed: 00:00:00.58

Execution Plan
----------------------------------------------------------
   0      DELETE STATEMENT Optimizer=FIRST_ROWS (Cost=43 Card=1)
   1    0   DELETE OF 'T1'
   2    1     TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=43 Card=1)




Statistics
----------------------------------------------------------
         30  recursive calls
      12118  db block gets
        213  consistent gets
        142  physical reads
    3975328  redo size
        441  bytes sent via SQL*Net to client
        537  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      10918  rows processed

它方便的一个重要原因是,当您需要刷新数百万行表中的数据,但又不想重新构建它时。“Delete *”会花费很长时间,而Truncate对性能的影响可以忽略不计。

在这个例子中,Truncate也可以被rollback

begin Tran
delete from  Employee

select * from Employee
Rollback
select * from Employee

DELETE语句可以有一个WHERE子句来删除特定的记录,而TRUNCATE语句不需要任何子句并擦除整个表。 重要的是,DELETE语句记录删除日期,而TRUNCATE语句不记录删除日期。

“Truncate不会记录任何东西”是正确的。我想更进一步:

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

截断比删除的速度优势应该是明显的。根据你的情况,这种优势从微不足道到巨大不等。

然而,我看到截断无意中破坏了引用完整性,并违反了其他约束。通过在事务外部修改数据而获得的权力,必须与在没有安全网的情况下走钢丝时继承的责任相平衡。