Mysql删除数据时要注意的坑


虽然我们不建议直接删除库里的数据,但有时候情况特殊,需要对库里数据进行删除,在行数据批量delete时,InnoDB如何处理自增ID的呢?我们可能在这里遇到一些删除方面的大坑,一起来看看吧。

【一】

    建表,设置自增列

mysql> CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL DEFAULT '' COMMENT '名称',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT='测试表';
Query OK, 0 rows affected (0.05 sec)


【二】

    指定id=1插入,锚定第一行是id是1

mysql> insert into test values(1,'wzx');
Query OK, 1 row affected (0.02 sec)


【三】

不指定id,依赖自增机制,插入3行(此时id应该变为2,3,4了?)

mysql> insert into test(name) values('wzx1'),('wzx2'),('wzx3');
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0


【四】

delete删除所有记录(注意这个删除)

mysql> delete from test;
Query OK, 4 rows affected (0.02 sec)


【五】

指定id=0插入

mysql> insert into test values(0,'wzx4');
Query OK, 1 row affected (0.02 sec)


【六】

指定id=1插入

mysql> insert into test values(1,'wzx5');
Query OK, 1 row affected (0.03 sec)


【七】

不指定id,依赖自增机制,插入1行

mysql> insert into test(name) values('wzx6');
Query OK, 1 row affected (0.02 sec)


【问】

此时表中的三行记录,id分别是多少?是否符合大家的预期?


【答】

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | wzx5 |
|  5 | wzx4 |
|  6 | wzx6 |
+----+------+
3 rows in set (0.15 sec)


【疑】

是不是和你们预期的结果不一致呢?我们来看看这是为什么?

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

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

    truncate table:删除所有数据,同时保留表,速度很快。(可以理解为,drop table然后再create table)

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

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

    ·    truncate是DDL语句,它不存在所谓的“事务回滚”;

        delete是DML语句,它执行完是可以rollback的。

    ·    truncate table返回值是0;

        delete from table返回值是被删除的行数。

    ·    InnoDB支持一个表一个文件,此时:

        truncate会一次性把表干掉,且不会激活触发器,速度非常快;

        delete from table则会一行一行删除,会激活触发器,速度比较慢。

        注意:delete数据,是要记录日志的,truncate表不需要记录日志。

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

        truncate会是失败;

        delete则会成功。

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

    ·    当表中有自增列是:

        truncate会使得自增列计数复原;

        delete所有数据后,自增列计数并不会从头开始。

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


上一篇

评论

登录后可发表评论