MySQL 中 truncate 和 delete 的坑
truncate 和 delete 是 MySQL 中清空表数据的两种方式,平常使用的时候两者好像都差不多,选谁都可以。实际上它们之间是有本质区别的,只有深入理解了它们的差异,在以后的开发中才能根据具体场景运用自如
思考
在讲 truncate 和 delete 的区别之前,不妨先看看下面的问题,带着问题边思考边往下看
问题:如上图所示,红色框(1) 和 红色框(2) 中 id 分别是多少
delete 简述
删除表全部数据或者部分数据,删除的时候是一行一行删除的,所以删除表全部数据时速度比较慢
语句后面可以用 where 过滤待删除的行,比较灵活
语句执行之后会返回本次删除的行数
truncate 简述
删除表全部数据,相当于先 drop table 然后 create table, 速度快
语句后面不能接过滤条件
语句执行后没有返回值,或者说返回值没有具体的含义
区别
虽然 truncate 和 delete 很相似,但它们有以下不同之处
-
delete 是一行一行的删除数据,truncate 是重新创建表,所以truncate 比 delete 速度快,特别是针对大表
-
其他表有引用本表列的外键时,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 语句直到删除的行数小于单次删除的最大值
答案
有了前面的介绍之后,思考题的答案应该很清晰了
因为表 tt
中 id
列是自增的,truncate 清空表数据会重置自增列,而 delete则不会,所以思考题中 (1) 处的 id 分别是 3、4, (2) 处的 id 分别是 1,2
具体的输出如下图: