详解MySQL三种删除数据的方式delete、drop 和 truncate

作者 : MOOMOO 本文共1144个字,预计阅读时间需要3分钟 发布时间: 2022-04-26 共15人阅读

在 MySQL 中有三种删除数据的方式,分别是 delete、drop 和 truncate。这三种方式用起来简单,但是背后的原理和注意事项估计大部分开发人员并不清楚,下面我就来讲解以下原理以及注意事项。

一、delete

Delete 是数据库 DML 操作语言,在删除数据的时候只会删除数据而不会删除数据所在表的表结构,并且在删除数据的时候会执行事务和触发 trigger。如果你所使用的数据库引擎是 InnoDB 的话 MySQL 会在执行删除数据操作的时候只把数据标注为已删除而不去真正的删除数据,因此在这种情况下如果你使用 Delete 删除数据的时候会发现表大小并没有改变

但是当你的数据库引擎是 MyISAM 的情况下执行 delete from table  语句会真正的删除表中的数据并释放空间的,如果你想让 MyISAM 引擎也能实现 InnoDB 引擎假删除的效果的话,你需要在 Delete 语句后面加上 Where 条件即可,例如 delete from user where age<18 这段语句执行后符合条件的数据将会标注为删除,并且所占空间不释放。

我们在用 Delete 语句删除数据的时候需要注意的是 delete 操作是一行一行的执行的,并且会将每行的删除日志记录在 redo 和 undo 表空间中以方便日后回滚和重做,这样就产生大量的日志进而占用大量的空间。因此当我们在进项大规模删除数据的时候不建议使用 Delete 语句。

Tip:虽说被删除数据所占用的空间没有被释放,但是当我们执行 Insert 操作的时候这部分未被释放的空间依然是可以被重用的。

二、truncate 和 drop

truncate 是数据库 DDL 语言,它和 delete 最大的区别是它不走事务数据也不放在 rollback segment 里,同时也不会触发 trigger, 因此执行 truncate 语句后会立即删除指定的数据且无法恢复,并释放数据所占用的空间(不管使用的数据库引擎是 InnoDB 还是 MyISAM )。truncate 语句一般用于快速清空表中数据并需要重置 auto_increment 值的情况。

关于重置 auto_increment 值需要有如下两点注意:

  • 在使用 InnoDB 引擎的情况下如果使用 truncate autoincrement会重置为 1 ,但是如果使用 delete 则 autoincrement 在重启 MySQL 之前仍然保持当前值,重启值后重置为 1 ;
  • 在使用 MyISAM 引擎的情况下如果使用 truncate autoincrement会重置为 1 ,但如果使用 delete 则 autoincrement 仍然保持当前值。

drop 同样是数据库 DDL 语言,它和 truncate 一样一旦执行将立即删除表和依赖、触发器、索引,但是会保留和该表相关的存储过程以及函数,并将状态改为 invalid 。

1. 本站所有资源来源于用户上传和网络,仅作为演示数据,如有侵权请邮件联系站长!
2. 本站客服QQ:4387159,唯一购买官网:MooMoo.TOP!
3. 盗版,破解有损他人权益和违法作为,请各位会员支持正版!
MooMoo » 详解MySQL三种删除数据的方式delete、drop 和 truncate

常见问题FAQ

是否支持共享资源赚外快?
本站支持任何人上传资源,一经采用即可展示,客户下单既有80%的提成。
可以免费教技术吗?
大家可以加群进行探讨共同进步,重点问题管理员会进行解答。
我是小白要怎么学习?
加群后做到不耻下问,看文章学习演示就能成为大佬。

发表评论