MySQL删除数据的三种方式!!!(有超级大坑)

数据库 MySQL
今天花一分钟,说说使用truncate与delete批量删除数据的异同。

行数据批量delete时,InnoDB如何处理自增ID的?

这里有一个潜在的大坑。

整个实验步骤如上图:

  • 第一步:建表,设定自增列;
  • 第二步:指定id=1插入,锚定第一行是id是1;
  • 第三步:不指定id,依赖自增机制,插入3行;画外音:此时id应该变为2,3,4了?
  • 第四步:delete删除所有记录;画外音:坑就容易出在这里。
  • 第五步:指定id=0插入;
  • 第六步:指定id=1插入;
  • 第七步:不指定id,依赖自增机制,插入1行;

请问,此时表中的三行记录,id分别是多少?

是否符合大家的预期?

今天花1分钟,说说使用truncate与delete批量删除数据的异同。

批量删除数据有三种常见的方法:

(1) drop table:当不需要该表时,可以使用该方法。

(2) truncate table:删除所有数据,同时保留表,速度很快。

画外音:可以理解为,drop table然后再create table。

(3) delete from table:可以删除所有数据,也能保留表,但性能较差。也可以带where条件删除部分数据,灵活性强。

虽然truncate和delete都能够删除所有数据,且保留表,但他们之间是有明显差异的。

(1)

  • truncate是DDL语句,它不存在所谓的“事务回滚”;
  • delete是DML语句,它执行完是可以rollback的。

(2)

  • truncate table返回值是0;
  • delete from table返回值是被删除的行数。

(3) InnoDB支持一个表一个文件,此时:

  • truncate会一次性把表干掉,且不会激活触发器,速度非常快;
  • delete from table则会一行一行删除,会激活触发器,速度比较慢。

画外音:delete数据,是要记录日志的,truncate表不需要记录日志。

(4) 当表中有列被其它表作为外键(foreign key)时:

  • truncate会是失败;
  • delete则会成功。

画外音:这类数据删除失败很容易定位问题,因为报错提示简单易懂。

(5) 当表中有自增列时:

  • truncate会使得自增列计数复原;
  • delete所有数据后,自增列计数并不会从头开始。

画外音:因此,delete所有数据后,自增列计数的这个行为,往往不是用户想要的,所以是一个潜在坑。

这一分钟,有收获吗?

请根据自己的业务场景,选择删除数据的方式哟。

责任编辑:赵宁宁 来源: 架构师之路
相关推荐

2019-12-20 14:56:50

批量删除数据数据删除

2011-04-08 11:13:50

CISCO IOS令牌桶双桶

2017-07-14 15:07:23

2010-06-13 16:04:14

MySQL三种安装方式

2013-06-17 17:08:47

Windows PhoWP开发共享数据方式

2015-01-16 17:41:45

数据中心模块化

2012-07-17 09:16:16

SpringSSH

2017-07-03 18:24:39

MySQL数据冗余

2019-11-20 18:52:24

物联网智能照明智能恒温器

2021-11-05 21:33:28

Redis数据高并发

2014-12-31 17:42:47

LBSAndroid地图

2021-06-24 08:52:19

单点登录代码前端

2020-11-01 17:10:46

异步事件开发前端

2010-03-12 17:52:35

Python输入方式

2018-04-08 09:31:57

大数据

2011-05-18 15:08:03

mysql删除修改数据

2010-09-13 12:19:03

2023-08-22 07:05:34

PowerShellWindows

2010-08-24 09:43:33

2009-07-29 09:36:07

无线通信接入方式
点赞
收藏

51CTO技术栈公众号