MySQL数据库删除数据后自增ID不连续的问题及解决_Mysql

mysql数据库删除数据后自增id不连续

1.表中已经出现不连贯的数据id时

执行以下语句进行修改

set @auto_id = 0;
update 表名 set 自增字段名 = (@auto_id := @auto_id + 1);
alter table 表名 auto_increment = 1;

如果需要清空表的数据的话，最好使用truncate table 表名来删除，这样新增的数据自增id会从1开始，如果使用delete来删除，新增的数据会沿着之前的id进行自增。

如果使用的数据库管理软件是navicat，那可以选中表右键选择截断表，其效果和truncate的效果是一样的。

2.在删除时解决

//删除信息
    public void delete(int id) {
        try {
            preparedstatement ps = con.preparestatement("delete from books where id = ?");
            ps.setint(1, id);
            ps.executeupdate();
            preparedstatement pr = con.preparestatement("alter table books auto_increment = ?;");
            pr.setint(1, id - 1);
            pr.executeupdate();
 
        } catch (sqlexception e) {
            throw new runtimeexception(e);
        }
    }

mysql自增字段不连续的原因分析

造成自增字段不连续的原因

1）唯一键冲突导致自增字段值不连续

示例1：创建数据表tb_student3，插入导致唯一键冲突的记录后，在插入数据

mysql> create table tb_student3(
    -> id int primary key auto_increment,
    -> name varchar(20) unique key,
    -> age int default null);
query ok, 0 rows affected (0.02 sec)

mysql> insert into tb_student3 values(1,'1','1');
query ok, 1 row affected (0.01 sec)

mysql> insert into tb_student3 values(null,'1','1');
error 1062 (23000): duplicate entry '1' for key 'name'

error 1062 (23000): duplicate entry '1' for key 'name'
mysql> insert into tb_student3 values(null,'2','1');
query ok, 1 row affected (0.01 sec)

mysql> select * from tb_student3;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | 1    |    1 |
|  3 | 2    |    1 |
+----+------+------+
2 rows in set (0.00 sec)

由于name字段有唯一键约束，当插入相同内容的字段时，会报 duplicate key error（唯一键冲突）。

在这之后，在插入新数据时，，自增 id 就是 3，这样就出现了自增字段值不连续的情况。

2）删除字段导致自增字段值不连续

示例2:创建数据表tb_student4，删除新增的数据后，再次新增数据

#创建新表
mysql> create table if not exists tb_student4(
    -> id int not null primary key auto_increment,
    -> name varchar(10) not null);
query ok, 0 rows affected (0.02 sec)

#新增字段
mysql> insert into tb_student4(name) values('java'),('python');
query ok, 2 rows affected (0.01 sec)
records: 2  duplicates: 0  warnings: 0

#查看表内容
mysql> select * from tb_student4;
+----+--------+
| id | name   |
+----+--------+
|  1 | java   |
|  2 | python |
+----+--------+
2 rows in set (0.00 sec)

#删除字段 name='python'
mysql> delete from tb_student4 where name='python';
query ok, 1 row affected (0.01 sec)

#查看表内容
mysql> select * from tb_student4;
+----+------+
| id | name |
+----+------+
|  1 | java |
+----+------+
1 row in set (0.00 sec)

#插入表数据
mysql> insert into tb_student4(name) values('mysql'),('html');
query ok, 2 rows affected (0.01 sec)
records: 2  duplicates: 0  warnings: 0

#查看表内容
mysql> select * from tb_student4;
+----+-------+
| id | name  |
+----+-------+
|  1 | java  |
|  3 | mysql |
|  4 | html  |
+----+-------+
3 rows in set (0.00 sec)

可以看出，删除字段后，自增字段不会补齐而是按照既定数值继续向下排列，会导致自增数字不连续。

3）其他

还有一些情况会造成自增不连续，比如事务回滚导致的自增键不连续、自增锁优化带来的不连续等。

解决方法

执行以下语句就可以解决

set @i=0;

update `tablename` set `id`=(@i:=@i+1);

alter table `tablename` auto_increment=0

我们执行上面由于唯一键冲突导致自增不连续的数据表,会发现id字段的自增连续了。

mysql> set @i=0;
query ok, 0 rows affected (0.00 sec)
mysql> update `tb_student3` set `id`=(@i:=@i+1);
query ok, 1 row affected (0.02 sec)
rows matched: 2  changed: 1  warnings: 0
mysql> alter table `tb_student3` auto_increment=0
    -> ;
query ok, 0 rows affected (0.01 sec)
records: 0  duplicates: 0  warnings: 0mysql> select * from tb_student3;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | 1    |    1 |
|  2 | 2    |    1 |
+----+------+------+
2 rows in set (0.00 sec)

附：

如果想要清空表的话可以使用truncate table 'good'语句来操作,比delete效率高,并且会将自增归零