注册 登录
  • 欢迎访问"运维那点事",推荐使用Google浏览器访问,可以扫码关注本站的"微信公众号"。
  • 如果您觉得本站对你有帮助,那么可以扫码捐助以帮助本站更好地发展。

SQL语句之truncate和drop以及delete命令的区别

MySQL SQL 彭东稳 7316次浏览 已收录 0个评论

一、环境

创建一个表s_user(有主键且自增)

插入几行数据

创建一个表(有外键到s_user)

二、需求分析

由于s_user有自增长,现在我想清空s_user表的信息并且把自增长给清空。一般执行truncate命令可以满足此需求,会把自增长给清空。但是在被其他表有外键关联的情况下,是不允许此操作的,以免破坏数据一致性。如下操作:

但MySQL中有一个变量可以解决此问题。

表示关闭外键检查,执行完truncate后,再打开即可。

三、truncate、drop、delete操作

现在来分别进行SQL语句的操作truncate、drop、delete,看看各自会出现的状况:

truncate

drop

delete

可以看到有外键关联的情况下,执行这三种命令会出现三种执行结果。有了直观的认识后,我们接着来说说这几个命令的区别,就知道了为什么又外键关联的情况下,会报如上错误。

四、truncate、drop、delete区别介绍

相同点:

1)truncate和delete以及drop都会删除表内的数据。

2)drop、truncate都是DDL语句(数据定义语言),执行后会自动提交。而delete属于DML(数据操作语言),在事务中,提交才会生效。

不同点:

1)truncate 和 delete 只删除数据不删除表的结构(定义)

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

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

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

3)delete语句不影响表所占用的extent,高水线(high watermark)保持原位置不动

drop语句将表所占用的空间全部释放。

truncate语句缺省情况下见空间释放到minextents个extent,除非使用reuse storage;truncate会将高水线复位(回到最开始)。

4)速度对比

一般来说:drop> truncate > delete

5)安全性:小心使用drop和truncate,尤其没有备份的时候。

使用上,想删除部分数据行用delete,注意带上where子句,回滚段要足够大。

想删除表,当然用drop。

想保留表而将所有数据删除,如果和事务无关,用truncate即可。如果和事务有关,或者想触发trigger,还是用delete。

6)TRUNCATE TABLE在功能上与不带WHERE子句的DELETE语句相同:二者均删除表中的全部行。但TRUNCATE TABLE比DELETE速度快,且使用的系统和事务日志资源少。DELETE语句每次删除一行,并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE通过释放存储表数据所用的数据页来删除数据,并且只在事务日志中记录页的释放。

7)TRUNCATE TABLE删除表中的所有行,但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值,请改用DELETE。如果要删除表定义及其数据,请使用DROP TABLE 语句。

8)对于由FOREIGN KEY约束引用的表,不能使用TRUNCATE TABLE,而应使用不带WHERE 子句的DELETE 语句,但是如果想清空自增长,必须使用truncate的话,那就按照上面的方法,关闭外键约束(foreign_key_checks=0)。因为不记录在日志中,所以它不能激活触发器。 为了实现高性能,TRUNCATE TABLE绕过删除数据的DML方法。因此,无法回滚,它不会导致ON DELETE触发器的触发,所以无法执行外键操作。

9)TRUNCATE TABLE不能用于参与了索引视图的表。


如果您觉得本站对你有帮助,那么可以支付宝扫码捐助以帮助本站更好地发展,在此谢过。
喜欢 (2)or分享 (0)
关于作者:

您必须 登录 才能发表评论!