sql语句中的drop, truncate, delete

参考：

https://blog.csdn.net/lovezhaohaimig/article/details/80184994

https://www.cnblogs.com/xianyao/p/11613021.html

1.drop table 表名称

drop (删除表)：删除表内容和表定义，释放空间。

简单来说就是把整个表去掉，以后要新增数据是不可能的，除非新增一个表。

把表的结构也删除了，下次要使用的时候要重新创建表的结构再插入数据。

drop语句将删除表结构被依赖的约束(constrain)，触发器(trigger)，索引(index)；依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。

不能回滚

2.truncate table 表名称

truncate (清空表中的数据)：删除表内容、释放空间但不删除表定义。与drop不同的是，他只是清空表数据而已，不删除表结构。其列、约束、索引等保持不变。

truncate table test 后，向test表添加数据，插入的字段的id重新从1开始递增 1、2、3.....（体现了truncate删除是释放空间）

in MySQL, resets auto_increment PKs

不能回滚

注意:truncate 不能删除行数据,要删就要把表清空。

3.delete from 表名称 where 列名称 = 值

delete (删除表中的数据)：删除整表中的行，表结构不会删除。

删除内容，不删除定义，不释放空间。

用delete删除数据，然后添加。可以看到添加之后id标识不连续。（说明delete删除不释放空间）

如果重新插入数据时，对应的id在上次基础之上递增 4、5、6....

delete语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存,以便进行回滚操作。

即不带where的delete: 删除表内容，不删除表结构

可回滚

4.总结

• 执行速度，一般来说: drop> truncate > delete。

• delete语句是数据库操作语言(dml)，这个操作会放到 rollback segment中，事务提交之后才生效；如果有相应的 trigger，执行的时候将被触发。

• truncate、drop 是数据库定义语言(ddl)，操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发 trigger。

• 在实际应用中，三者的区别是明确的。

  • 当你不再需要该表时， 用 drop;

  • 当你仍要保留该表，但要删除所有记录时， 用 truncate;

  • 当你要删除部分记录时（always with a WHERE clause), 用 delete.

• truncate 与delete 比较：

  • truncate table 在功能上与不带 WHERE 子句的 delete语句相同：二者均删除表中的全部行。

  • truncate 比 delete速度快，且使用的系统和事务日志资源少。

  • truncate 操作后的表比Delete操作后的表要快得多。

  • 当表被清空后，表和表的索引将重新设置成初始大小，而delete则不能。

  • truncate 删除不能恢复，delete 可以恢复数据

(http://www.itpub.net/thread-392126-1-1.html)

问：执行truncate table，除了rows会删除，index也会删除，但我执行之后，查看user_indexes，索引仍在，重新加入新资料再做查询，一样可以用到索引，是不是这里所说的删除，是指删除索引的内容，可是一般我们用delete，索引的内容不是也会跟着删除吗? 那么用truncate和delete，索引的删除又有什么不同呢?

答：你truncate后，你只是删除索引相应的数据，也就是你所说的内容，索引的定义并没有删除，因此在数据字典里面还有相关信息。重新插入数据后，当然还会用到原来定义的索引。你要真正删除一个索引的话，那么得用drop index index_name把索引DROP掉。delete的话，索引的内容跟truncate一样是删除的，只是物理上事实上并没有删除，这个你不要去管，你就知道索引条目也相应删除就是了。