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

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

我想对matthieu的帖子发表评论，但我还没有得到代表…

在MySQL中，自动递增计数器通过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

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

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

begin Tran
delete from  Employee

select * from Employee
Rollback
select * from Employee

最大的区别是truncate是不记录日志的操作，而delete是。

简单地说，这意味着在数据库崩溃的情况下，不能通过截断恢复所操作的数据，但可以通过删除恢复。

详情请点击这里

对原始答案的一个小修正——删除也会产生大量的重做(因为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