Truncate的使用详解

删除表中数据的方法有 delete 和 truncate, 其中TRUNCATE TABLE用于删除表中的所有行，而不记录单个行删除操作；TRUNCATE TABLE 与没有 WHERE 子句的 DELETE 语句类似，但是，TRUNCATE TABLE 速度更快，使用的系统资源和事务日志资源更少。下面介绍Truncate的用法

1.truncate使用语法

Truncate 语法能够快速清空数据表内所有数据，并且能针对具有自动递增值的字段，做计数重置归零重新计算的作用，只能作用于表。

Truncate的语法很简单，后面直接跟表名即可，例如： truncate table tbl_name 或者 truncate tbl_name 。

执行truncate语句时需要拥有表的drop权限，从逻辑上讲，truncate table类似于delete删除所有行的语句或 drop table然后再create table语句的组合。为了实现高性能，它绕过了删除数据的DML方法，因此，它不能回滚。尽管truncate table与delete相似，但它被分类为DDL语句而不是DML语句。

2.truncate与drop,delete的对比

truncate 与 delete、drop 三者之间的异同：

• delete语句是DML语言，操作后会放在 rollback segement 中，事物提交后才生效，如果有相应的触发器(trigger)，执行时将被触发，可回滚。truncate、drop 是DDL语言，执行后会自动提交立即生效，原数据不会放到 rollback中，不能回滚，操作不会触发trigger。
• Delete 语句每次删除一行，并在事务日志中为所删除的每行记录一项。Truncate Table 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。
• truncate和 delete 只删除表的数据(定义)，表结构及其约束、索引等保持不变；drop语句将删除表的结构、被依赖的约束(constrain)、触发器 (trigger)、索引(index)，依赖于该表的存储过程/函数将保留，但是变为invalid状态。
• truncate后会使表和索引所占用的空间会恢复到初始大小；delete操作不会减少表或索引所占用的空间，drop语句将表所占用的空间全释放掉。
• Truncate Table 在功能上与不带 Where子句的 Delete 语句相同：二者均删除表中的全部行，但 Truncate Table 比 Delete 速度快，且使用的系统和事务日志资源少。
• truncate只能作用于表；delete，drop可作用于表、视图。truncate 清空表中所有数据；drop一般用于删除整体性数据 ，如表、模式、索引、视图、完整性限制等；delete用于删除局部性数据 如表中的某一元组。
• 在安全性方面，要谨慎使用 drop、truncate，特别是没有做备份的情况下：如何表数据完全不需要时可以用truncate；如果想删除部分数据可使用 delete 需要带上 where子句，回滚段要足够大；如果想删除表可以用 drop；如果想保留表而将所有数据删除且和事务无关，用truncate即可；如果和事物有关，或者想触发 trigger，则使用delete；如果是整理表内部的碎片，可以用 truncate 跟上 reuse stroage，再重新导入、插入数据。

3.truncate注意事项

无论怎样，truncate表都是高危操作，特别是在生产环境要更加谨慎使用，下面列出几点注意事项。

• Truncate Table 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 Delete 。如果要删除表定义及其数据，请使用 DROP TABLE 语句。
• 对于由 Foreign Key (外键)  约束引用的表，不能使用 Truncate Table，而应使用不带 Where子句的 Delete 语句。由于 Truncate Table 不记录在日志中，所以它不能激活触发器。
• Truncate Table 不能用于参与了索引视图的表。
• 对用 Truncate Table 删除数据的表增加数据时，要使用Update Statistics 来维护索引信息。
• 如果有 Rollback 语句，Delete 操作将被撤销，但 Truncate 不会撤销。
• 执行 Truncate 需要 drop权限，不建议给账号drop权限；执行 Truncate 前一定要再三检查确认，最好提前备份下表数据。

4.truncate不宜使用场景

• 由 FOREIGN KEY 约束引用的表（可以截断具有引用自身的外键的表）
• 参与索引视图的表
• 通过使用事务复制或合并复制发布的表
• 对于具有以上一个或多个特征的表，请使用 DELETE 语句
• TRUNCATE TABLE 不能激活触发器，因为该操作不记录各个行删除