造成自增字段不连续的原因
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: 0
mysql> select * from tb_student3;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | 1 | 1 |
| 2 | 2 | 1 |
+----+------+------+
2 rows in set (0.00 sec)
附:
如果想要清空表的话可以使用TRUNCATE table 'good'
语句来操作,比delete效率高,并且会将自增归零