MySQL中的删除:delete、drop、Truncate三者的区别

MySQL中delete、drop、Truncate的区别

  • 三者区别:
  • 三者图解:
  • 三者详解
    • delete
    • drop
    • Truncate
    • 执行速度
    • 安全性
    • 使用建议

相同点:都具有删除功能。

三者区别:

delete truncate drop
类型 属于DML 属于DDL 属于DDL
回滚 可回滚 不可回滚 不可回滚
删除内容 删除表的全部或者一部分数据行 删除表中的所有数据 从数据库中删除表
删除速度 删除速度慢 删除速度快 删除速度最快

三者图解:

MySQL中的删除:delete、drop、Truncate三者的区别_第1张图片

三者详解

delete

删除内容,不删除表结构,不释放空间。

用法:

-- 删除整表数据
DELETE FROM table_name

-- 删除部分数据
DELETE FROM table_name
WHERE condition

1、DELETE属于数据库DML操作语言,只删除数据不删除表的结构,会走事务,执行时会触发trigger;
2、在 InnoDB 中,DELETE其实并不会真的把数据删除,MySQL实际上只是给删除的数据打了个标记为已删除,因此 delete 删除表中的数据时,表文件在磁盘上所占空间不会变小,存储空间不会被释放,只是把删除的数据行设置为不可见。虽然未释放磁盘空间,但是下次插入数据的时候,仍然可以重用这部分空间(重用 → 覆盖)。
3、DELETE执行时,会先将所删除数据缓存到rollback segement中,事务commit之后生效;
4、delete from table_name删除表的全部数据,对于MyISAM 会立刻释放磁盘空间,InnoDB 不会释放磁盘空间;
5、对于delete from table_name where xxx 带条件的删除, 不管是InnoDB还是MyISAM都不会释放磁盘空间;
6、delete操作以后使用 optimize table table_name 会立刻释放磁盘空间。不管是InnoDB还是MyISAM 。所以要想达到释放磁盘空间的目的,delete以后执行optimize table 操作。

drop

drop语句删除表结构及所有数据,并将表所占用的空间全部释放。

用法:

-- 删除整表数据
DROP TABLE table_name

1、drop:属于数据库DDL定义语言,同Truncate;执行后立即生效,无法找回
2、drop table table_name 立刻释放磁盘空间 ,不管是 InnoDB 和 MyISAM;
3、drop 语句将删除表的结构被依赖的约束(constrain)、触发器(trigger)、索引(index); 依赖于该表的存储过程/函数将保留,但是变为 invalid 状态。

Truncate

删除表中的内容,不删除表结构,释放空间。

用法:

-- 删除整表数据
TRUNCATE TABLE table_name

1、truncate属于数据库DDL定义语言,不走事务,原数据不放到 rollback segment 中,操作不触发 trigger,执行后立即生效,无法找回。
2、truncate table table_name 立刻释放磁盘空间 ,不管是 InnoDB和MyISAM 。truncate table其实有点类似于drop table 然后create,只不过这个create table 的过程做了优化,比如表结构文件之前已经有了等等。所以速度上应该是接近drop table的速度。
3、truncate能够快速清空一个表。并且重置auto_increment的值。但对于不同的类型存储引擎需要注意的地方是:对于MyISAM,truncate会重置auto_increment(自增序列)的值为1。而delete后表仍然保持auto_increment。对于InnoDB,truncate会重置auto_increment的值为1。delete后表仍然保持auto_increment。但是在做delete整个表之后重启MySQL的话,则重启后的auto_increment会被置为1。也就是说,InnoDB的表本身是无法持久保存auto_increment。delete表之后auto_increment仍然保存在内存,但是重启后就丢失了,只能从1开始。实质上重启后的auto_increment会从 SELECT 1+MAX(ai_col) FROM t 开始。

执行速度

drop> truncate > delete

安全性

小心使用drop 和truncate,尤其没有备份的时候。
使用上,想删除部分数据行用delete,注意带上where子句. 回滚段要足够大。

使用建议

完全删除表[drop]
想保留表而将所有数据删除. 如果和事务无关[truncate]
如果和事务有关,或者想触发trigger[delete]

你可能感兴趣的