|
|
|
|
公众号矩阵

MySQL:删除操作Delete、Truncate、Drop用法比较

今天小编给大家梳理一下MYSQL删除操作Delete、Truncate、Drop用法有什么区别,到底该如何合理使用,希望对大家能有帮助!

作者: 郝光明 来源: IT技术分享社区|2021-10-12 06:56

今天小编给大家梳理一下MySQL删除操作Delete、Truncate、Drop用法有什么区别,到底该如何合理使用,希望对大家能有帮助!

1、执行速度比较

Delete、Truncate、Drop关键字都可以删除数据

drop>truncate>delete

2、原理方面

2.1 delete

delete属于数据库DML操作语言,只会删除数据表中的记录,会执行事务,执行的时候也会触发触发器。

InnoDB数据库引擎中,执行delete操作只会给删除的记录打上了删除标记,并不会真正删除数据,只是把删除的数据记录设置为不可见,不会释放磁盘空间,如果插入新的数据可以覆盖该部分空间。

如果开启事务的话,执行delete操作,会先将要删除数据缓存到rollback segement中,等事务commit之后才生效。

delete from table_name 不带查询条件会删除表的全部数据,MyISAM引擎会立刻释放磁盘空间,InnoDB 不会释放磁盘空间;如果带查询条件的话都不会释放磁盘空间,可以执行optimize table table_name 会立刻释放磁盘空间。建议如果需要释放存储空间的话可以执行delete后,然后执行optimize table table_name 语句达到清理磁盘空间的目的。

-- 查询数据库test对应的表t_user 占用的磁盘空间

  1. select concat(round(sum(DATA_LENGTH/1024/1024),2),'M'as table_size  
  2.  
  3. from information_schema.tables  
  4.  
  5. where table_schema='test' AND table_name='t_user'

说明:delete 操作是逐行执行删除的,并且同时将每行的的删除操作日志记录在redo和undo表空间中去,便于进行回滚(rollback)和重做操作,因此生成的大量操作日志也会占用磁盘空间。

2.2 truncate

truncate是数据库DDL定义语言,不受事务影响,也不会触发 trigger。执行操作后会立即生效,无法找回删除的数据。

执行truncate table table_name 会立刻释放磁盘空间 ,不管是 InnoDB和MyISAM 都一样 。

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 开始。

使用truncate操作的时候要最好备份表,避免出现不可挽回的情况。

2.3 drop

drop属于数据库DDL定义语言,和truncate一样。执行后会立即生效,不可恢复。

drop table table_name 执行成功后不管是MyISM还是InnoDB都会立刻释放磁盘空间 ,并且会删除该数据表上依赖的约束(constrain)、触发器(trigger)、索引(index);  依赖于该表的存储过程/函数将保留,但是会变为失效状态。

总结

在工作当中执行数据库删除的时候一定要慎重再慎重,建议每次进行数据删除的使用最好数据表的备份工作,这样就会大大减少你删除跑路的几率。很多时候不要过于相信自己的动手能力,老虎还有打盹的时候,万一手滑了呢。尽可能养成好的数据库运维习惯,这样会让自己少跌跟头,你的事业才会更加顺利。

本文转载自微信公众号「IT技术分享社区」,可以通过以下二维码关注。转载本文请联系IT技术分享社区公众号。

个人博客网站:https://programmerblog.xyz

【编辑推荐】

  1. 鸿蒙官方战略合作共建——HarmonyOS技术社区
  2. 广大应用程序开发者正努力支持Windows 11的新右键上下文菜单
  3. OpenHarmony Neptune开发板-MQTT连接华为IoT平台
  4. 用上强化学习和博弈论,EA开发的测试AI成精了
  5. 国内即将迎来区块链开发公司兴起的时代
  6. Vue.js进阶技巧实战开发
【责任编辑:武晓燕 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

订阅专栏+更多

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO官微