SQL中drop、truncate和delete的区别

目录

• 使用范围
• 功能
• 日志与触发器
• 表和索引所占空间
• 速度
• 安全性

使用范围

• TRUNCATE只能对TABLE；
• DELETE可以是table和view,并且可以带where子句。

不能对以下表使用 TRUNCATE TABLE：

• 由 FOREIGN KEY 约束引用的表。（您可以截断具有引用自身的外键的表。）
• 参与索引视图的表。
• 通过使用事务复制或合并复制发布的表。

功能

• truncate删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。
• delete 仅删除表中的数据。
• drop 语句将删除表的结构被依赖的约束(constrain)、触发器(trigger)、索引(index)；依赖于该表的存储过程/函数将保留,但是变为 invalid 状态。

日志与触发器

• delete 语句是数据库操作语言(DML)，这个操作会放到 rollback segement 中，事务提交之后才生效；如果有相应的 trigger，执行的时候将被触发。

  DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。

• truncate、drop 是数据库定义语言(DDL)，操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发 trigger。

表和索引所占空间

• 当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小，
• 而DELETE操作不会减少表或索引所占用的空间。
• drop语句将表所占用的空间全释放掉。

TRUNCATE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

速度

• drop > truncate > delete

安全性

• delete > truncate > drop