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: 0mysql> 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)