MySQL 中 truncate 和 delete 的坑

truncate 和 delete 是 MySQL 中清空表数据的两种方式,平常使用的时候两者好像都差不多,选谁都可以。实际上它们之间是有本质区别的,只有深入理解了它们的差异,在以后的开发中才能根据具体场景运用自如

思考

在讲 truncate 和 delete 的区别之前,不妨先看看下面的问题,带着问题边思考边往下看

MySQL 中 truncate 和 delete 的坑

问题:如上图所示,红色框(1) 和 红色框(2) 中 id 分别是多少

delete 简述

删除表全部数据或者部分数据,删除的时候是一行一行删除的,所以删除表全部数据时速度比较慢

语句后面可以用 where 过滤待删除的行,比较灵活

语句执行之后会返回本次删除的行数

truncate 简述

删除表全部数据,相当于先 drop table 然后 create table, 速度快

语句后面不能接过滤条件

语句执行后没有返回值,或者说返回值没有具体的含义

区别

虽然 truncate 和 delete 很相似,但它们有以下不同之处

  • delete 是一行一行的删除数据,truncate 是重新创建表,所以truncate 比 delete 速度快,特别是针对大表

  • 其他表有引用本表列的外键时,truncate 会执行失败,delete 可以执行

MySQL 中 truncate 和 delete 的坑

上图中 tb 表的 idb 列是ta表的ida列的外键引用,truncate table ta 执行失败, ``delete from ta```能执行成功

  • truncate 是DDL语句,所以不能回滚,delete 是DML语句,可以回滚

  • truncate 返回值无意义, delete 返回本次删除的行数

  • 针对 AUTO_INCREMENT 列,truncate tablename 会把列值重置为起始值,而 delete from tablename 则不会重置(上面的思考题用到了这条知识点)

delete 的优化

前面提到使用 delete 清空大表是速度比较慢,为了保证 delete 语句不会占用大量的时间,可以使用 limit row_count 语句来指定单次删除的最大行数,如果删除的行数大于等于单次删除的最大行数,重复执行 delete 语句直到删除的行数小于单次删除的最大值

答案

有了前面的介绍之后,思考题的答案应该很清晰了

因为表 ttid 列是自增的,truncate 清空表数据会重置自增列,而 delete则不会,所以思考题中 (1) 处的 id 分别是 3、4, (2) 处的 id 分别是 1,2

具体的输出如下图:

MySQL 中 truncate 和 delete 的坑

MySQL 中 truncate 和 delete 的坑

上一篇:[MySQL]innodb_flush_log_at_trx_commit字段


下一篇:SQL Server中的集合运算: UNION, EXCEPT和INTERSECT