MySQL數(shù)據(jù)庫刪除數(shù)據(jù)后自增ID不連續(xù)的問題及解決

更新時間：2024年06月05日 10:11:08 作者：迷失自我的小鹿

這篇文章主要介紹了MySQL數(shù)據(jù)庫刪除數(shù)據(jù)后自增ID不連續(xù)的問題及解決,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教

MySQL數(shù)據(jù)庫刪除數(shù)據(jù)后自增ID不連續(xù)

1.表中已經(jīng)出現(xiàn)不連貫的數(shù)據(jù)ID時

執(zhí)行以下語句進行修改

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

如果需要清空表的數(shù)據(jù)的話，最好使用TRUNCATE TABLE 表名來刪除，這樣新增的數(shù)據(jù)自增ID會從1開始，如果使用DELETE來刪除，新增的數(shù)據(jù)會沿著之前的ID進行自增。

如果使用的數(shù)據(jù)庫管理軟件是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自增字段不連續(xù)的原因分析

造成自增字段不連續(xù)的原因

1）唯一鍵沖突導致自增字段值不連續(xù)

示例1：創(chuàng)建數(shù)據(jù)表tb_student3，插入導致唯一鍵沖突的記錄后，在插入數(shù)據(jù)

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字段有唯一鍵約束，當插入相同內(nèi)容的字段時，會報 Duplicate key error（唯一鍵沖突）。

在這之后，在插入新數(shù)據(jù)時，，自增 id 就是 3，這樣就出現(xiàn)了自增字段值不連續(xù)的情況。

2）刪除字段導致自增字段值不連續(xù)

示例2:創(chuàng)建數(shù)據(jù)表tb_student4，刪除新增的數(shù)據(jù)后，再次新增數(shù)據(jù)

#創(chuàng)建新表
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

#查看表內(nèi)容
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)

#查看表內(nèi)容
mysql> SELECT * FROM tb_student4;
+----+------+
| id | name |
+----+------+
|  1 | JAVA |
+----+------+
1 row in set (0.00 sec)

#插入表數(shù)據(jù)
mysql> INSERT INTO tb_student4(name) VALUES('MYSQL'),('HTML');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

#查看表內(nèi)容
mysql> SELECT * FROM tb_student4;
+----+-------+
| id | name  |
+----+-------+
|  1 | JAVA  |
|  3 | MYSQL |
|  4 | HTML  |
+----+-------+
3 rows in set (0.00 sec)

可以看出，刪除字段后，自增字段不會補齊而是按照既定數(shù)值繼續(xù)向下排列，會導致自增數(shù)字不連續(xù)。

3）其他

還有一些情況會造成自增不連續(xù)，比如事務回滾導致的自增鍵不連續(xù)、自增鎖優(yōu)化帶來的不連續(xù)等。

解決方法

執(zhí)行以下語句就可以解決

SET @i=0;

UPDATE `tablename` SET `id`=(@i:=@i+1);

ALTER TABLE `tablename` AUTO_INCREMENT=0

我們執(zhí)行上面由于唯一鍵沖突導致自增不連續(xù)的數(shù)據(jù)表,會發(fā)現(xiàn)id字段的自增連續(xù)了。

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效率高,并且會將自增歸零