MySQL中delete|truncate|drop的区别

delete

1. 删除整张表

delete from table_name 

2. 也可以删除部分数据

delete from table_name where xxx;

3. 说明

• delete是属于DML语句，每次执行delete操作的时候，都会记录在redo和undo表空间里面，以便进行rollback，而且需要手动commit才能生效。
• 删除表中数据，但不删除表结构，也不释放空间。

---

truncate

1. 只能操作整张表

truncate table table_name;

2. 说明

• truncate是通过释放存储表数据所用的数据页来删除数据，并且重新设置高水线和所有的索引。
• truncate不会记录日志，所以执行速度很快，同时也不能通过rollback来撤销操作。对于有外键约束引用的表，不能使用truncate，会报错。
• truncate是DDL语句，所以操作会立即生效，删除内容、释放空间，但是不删除表结构。

---

drop

1. 操作整张表

drop table table_name;

2. 说明

• 删除之后，依赖于该表的存储过程/函数将保留,但是变为invalid状态。
• drop也属于DDL语言，立即执行，执行速度最快。
• 删除内容和定义，释放空间。

---

区别

1. 当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小；DELETE操作不会减少表或索引所占用的空间；DROP语句将表所占用的空间全释放掉。
2. 执行速度： drop > truncate > delete
3. delete from删空表后，会保留一个空的页，truncate在表中不会留有任何页。
4. truncate语句实际是删除原来的表然后重新建立一个新表。
5. 如果有identity产生的自增id列，delete from后仍然从上次的数开始增加，即种子不变；使用truncate删除之后，种子会恢复到初始值。

---

参考

1. MySQL删除表操作实现（delete、truncate、drop的区别）
2. mysql之drop、truncate和delete的区别