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

爱谁谁滚犊子

MySQL数据库删除数据后自增ID不连续


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

执行以下语句进行修改

1. SET @auto_id = 0;
   
2. UPDATE 表名 SET 自增字段名 = (@auto_id := @auto_id + 1);
   
3. ALTER TABLE 表名 AUTO_INCREMENT = 1;

复制代码

如果需要清空表的数据的话，最好使用TRUNCATE TABLE 表名来删除，这样新增的数据自增ID会从1开始，如果使用DELETE来删除，新增的数据会沿着之前的ID进行自增。
如果使用的数据库管理软件是Navicat，那可以选中表右键选择截断表，其效果和TRUNCATE的效果是一样的。

2.在删除时解决

1. //删除信息
   
2.     public void delete(int id) {
   
3.         try {
   
4.             PreparedStatement ps = con.prepareStatement("delete from books where id = ?");
   
5.             ps.setInt(1, id);
   
6.             ps.executeUpdate();
   
7.             PreparedStatement pr = con.prepareStatement("alter table books auto_increment = ?;");
   
8.             pr.setInt(1, id - 1);
   
9.             pr.executeUpdate();
   
10. 
    
11.         } catch (SQLException e) {
    
12.             throw new RuntimeException(e);
    
13.         }
    
14.     }

复制代码

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


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

1）唯一键冲突导致自增字段值不连续
示例1：创建数据表tb_student3，插入导致唯一键冲突的记录后，在插入数据

1. mysql> CREATE TABLE tb_student3(
   
2.     -> id INT PRIMARY KEY AUTO_INCREMENT,
   
3.     -> name VARCHAR(20) UNIQUE KEY,
   
4.     -> age INT DEFAULT NULL);
   
5. Query OK, 0 rows affected (0.02 sec)
   
6. 
   
7. mysql> INSERT INTO tb_student3 VALUES(1,'1','1');
   
8. Query OK, 1 row affected (0.01 sec)
   
9. 
   
10. mysql> INSERT INTO tb_student3 VALUES(NULL,'1','1');
    
11. ERROR 1062 (23000): Duplicate entry '1' for key 'name'
    
12. 
    
13. ERROR 1062 (23000): Duplicate entry '1' for key 'name'
    
14. mysql> INSERT INTO tb_student3 VALUES(NULL,'2','1');
    
15. Query OK, 1 row affected (0.01 sec)
    
16. 
    
17. mysql> SELECT * FROM tb_student3;
    
18. +----+------+------+
    
19. | id | name | age  |
    
20. +----+------+------+
    
21. |  1 | 1    |    1 |
    
22. |  3 | 2    |    1 |
    
23. +----+------+------+
    
24. 2 rows in set (0.00 sec)

复制代码

由于name字段有唯一键约束，当插入相同内容的字段时，会报 Duplicate key error（唯一键冲突）。
在这之后，在插入新数据时， ，自增 id 就是 3，这样就出现了自增字段值不连续的情况。
2）删除字段导致自增字段值不连续
示例2:创建数据表tb_student4，删除新增的数据后，再次新增数据

1. #创建新表
   
2. mysql> CREATE TABLE IF NOT EXISTS tb_student4(
   
3.     -> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
   
4.     -> name VARCHAR(10) NOT NULL);
   
5. Query OK, 0 rows affected (0.02 sec)
   
6. 
   
7. #新增字段
   
8. mysql> INSERT INTO tb_student4(name) VALUES('JAVA'),('PYTHON');
   
9. Query OK, 2 rows affected (0.01 sec)
   
10. Records: 2  Duplicates: 0  Warnings: 0
    
11. 
    
12. #查看表内容
    
13. mysql> SELECT * FROM tb_student4;
    
14. +----+--------+
    
15. | id | name   |
    
16. +----+--------+
    
17. |  1 | JAVA   |
    
18. |  2 | PYTHON |
    
19. +----+--------+
    
20. 2 rows in set (0.00 sec)
    
21. 
    
22. #删除字段 name='PYTHON'
    
23. mysql> DELETE FROM tb_student4 WHERE name='PYTHON';
    
24. Query OK, 1 row affected (0.01 sec)
    
25. 
    
26. #查看表内容
    
27. mysql> SELECT * FROM tb_student4;
    
28. +----+------+
    
29. | id | name |
    
30. +----+------+
    
31. |  1 | JAVA |
    
32. +----+------+
    
33. 1 row in set (0.00 sec)
    
34. 
    
35. #插入表数据
    
36. mysql> INSERT INTO tb_student4(name) VALUES('MYSQL'),('HTML');
    
37. Query OK, 2 rows affected (0.01 sec)
    
38. Records: 2  Duplicates: 0  Warnings: 0
    
39. 
    
40. #查看表内容
    
41. mysql> SELECT * FROM tb_student4;
    
42. +----+-------+
    
43. | id | name  |
    
44. +----+-------+
    
45. |  1 | JAVA  |
    
46. |  3 | MYSQL |
    
47. |  4 | HTML  |
    
48. +----+-------+
    
49. 3 rows in set (0.00 sec)

复制代码

可以看出，删除字段后，自增字段不会补齐而是按照既定数值继续向下排列，会导致自增数字不连续。
3）其他
还有一些情况会造成自增不连续，比如事务回滚导致的自增键不连续、自增锁优化带来的不连续等。

解决方法

执行以下语句就可以解决

1. SET @i=0;
   
2. 
   
3. UPDATE `tablename` SET `id`=(@i:=@i+1);
   
4. 
   
5. ALTER TABLE `tablename` AUTO_INCREMENT=0

复制代码

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

1. mysql> SET @i=0;
   
2. Query OK, 0 rows affected (0.00 sec)
   
3. mysql> UPDATE `tb_student3` SET `id`=(@i:=@i+1);
   
4. Query OK, 1 row affected (0.02 sec)
   
5. Rows matched: 2  Changed: 1  Warnings: 0
   
6. mysql> ALTER TABLE `tb_student3` AUTO_INCREMENT=0
   
7.     -> ;
   
8. Query OK, 0 rows affected (0.01 sec)
   
9. Records: 0  Duplicates: 0  Warnings: 0mysql> select * from tb_student3;
   
10. +----+------+------+
    
11. | id | name | age  |
    
12. +----+------+------+
    
13. |  1 | 1    |    1 |
    
14. |  2 | 2    |    1 |
    
15. +----+------+------+
    
16. 2 rows in set (0.00 sec)

复制代码

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

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持脚本之家。

来源:https://www.jb51.net/database/32224460j.htm
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！