MYSQL笔记：删除操作Delete、Truncate、Drop用法比较

Dear 丶 2022-09-15 13:58 201阅读 0赞

![5a382f634528da92f697c6eb7e65568f.png][]

今天小编给大家梳理一下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 占用的磁盘空间
    select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') as table_size 
    from information_schema.tables 
    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技术分享社区

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

![bd27521cb02333432564d9832719561d.png][]

文章推荐[程序员效率：画流程图常用的工具][Link 1][程序员效率：整理常用的在线笔记软件][Link 2][远程办公：常用的远程协助软件，你都知道吗？][Link 3][51单片机程序下载、ISP及串口基础知识][51_ISP][硬件：断路器、接触器、继电器基础知识][Link 4]

[5a382f634528da92f697c6eb7e65568f.png]: /images/20220828/bd75897200604f6882ba159b8a864751.png
[bd27521cb02333432564d9832719561d.png]: /images/20220828/b8b970c5b19c406dbd766b522af976fb.png
[Link 1]: http://mp.weixin.qq.com/s?__biz=MzU5MjA5Njk0NQ==&mid=2247487684&idx=1&sn=008111bf1bcca6150ad6ddd48fd99711&chksm=fe25ab1ac952220cb0774f2a6da51141f901d18ca1ef6fde545376293b08dbaae45c6e671f2f&scene=21#wechat_redirect
[Link 2]: http://mp.weixin.qq.com/s?__biz=MzU5MjA5Njk0NQ==&mid=2247487660&idx=1&sn=381729ac34ab1e36a2be82f84bb5e164&chksm=fe25ab72c952226479a3a6545a858f2918be750031b0f4bfbb6a2f308f1f736258528f57e625&scene=21#wechat_redirect
[Link 3]: http://mp.weixin.qq.com/s?__biz=MzU5MjA5Njk0NQ==&mid=2247487655&idx=1&sn=40adeb081d70c7d656dc9dd793a32ed0&chksm=fe25ab79c952226f3f517ebac72a84c789bf4d1fdd352c0219e5f7c161cd2fd07de0994e1ec8&scene=21#wechat_redirect
[51_ISP]: http://mp.weixin.qq.com/s?__biz=MzU5MjA5Njk0NQ==&mid=2247487635&idx=1&sn=f19e9834a0a76efd52aa650c14c01ced&chksm=fe25ab4dc952225bd7976f2f77ef4162779b8a57bb7358c239357fddb1303c286b2f12d8c51f&scene=21#wechat_redirect
[Link 4]: http://mp.weixin.qq.com/s?__biz=MzU5MjA5Njk0NQ==&mid=2247487633&idx=1&sn=c402ef612600d618281b5c9b4ca200c4&chksm=fe25ab4fc9522259bd0af7cf83eeb85ddc49e27be6db77fce74f8bb25cc7ba2b227e75bc0463&scene=21#wechat_redirect