自增字段值跳变的原因

出现表中的自增字段取值不连续的情况，可能原因有以下几种：

初值与步长问题，步长不为1会导致自增字段取值不连续。

mysql> show variables like 'auto_inc%'; 
+--------------------------+-------+ 
| Variable_name            | Value | 
+--------------------------+-------+ 
| auto_increment_increment | 2     | 
| auto_increment_offset    | 1     | 
+--------------------------+-------+ 
mysql> select * from auto_test1; 
+----+ 
| id | 
+----+ 
|  2 | 
|  4 | 
|  6 | 
|  8 | 
+----+

直接修改表的AUTO_INCREMENT，会导致自增字段取值跳变。

mysql> select * from animals; 
+----+-----------+ 
| id | name      | 
+----+-----------+ 
|  1 | fish       | 
|  2 | cat       | 
|  3 | penguin   | 
+----+-----------+ 
mysql> show create table animals; 
+---------+-----------------------------------------------------+ 
| Table   | Create Table                                                   | 
+---------+-----------------------------------------------------+ 
| animals | CREATE TABLE `animals` (   
`id` mediumint NOT NULL AUTO_INCREMENT,   
`name` char(30) NOT NULL,   
PRIMARY KEY (`id`) ) 
ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 | 
+---------+-----------------------------------------------------+ 
mysql> alter table animals AUTO_INCREMENT=100; 
Query OK, 0 rows affected (0.04 sec) 
Records: 0  Duplicates: 0  Warnings: 0 
mysql> show create table animals; 
+---------+-----------------------------------------------------+ 
| Table   | Create Table                                        | 
+---------+-----------------------------------------------------+ 
| animals | CREATE TABLE `animals` (   
`id` mediumint NOT NULL AUTO_INCREMENT,   
`name` char(30) NOT NULL,   
PRIMARY KEY (`id`) 
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8         | 
+---------+-----------------------------------------------------+ 
mysql> INSERT INTO animals (id,name) VALUES(0,'rabbit'); 
Query OK, 1 row affected (0.00 sec)  
mysql> select * from animals; 
+-----+-----------+ 
| id  | name      | 
+-----+-----------+ 
|   1 | fish       | 
|   2 | cat       | 
|   3 | penguin   | 
| 100 | rabbit    | 
+-----+-----------+ 
9 rows in set (0.00 sec)

插入数据时直接指定自增字段的取值，会导致自增字段取值跳变。

mysql> select * from animals; 
+----+-----------+ 
| id | name      | 
+----+-----------+ 
|  1 | fish       | 
|  2 | cat       | 
|  3 | penguin   | 
+----+-----------+ 
mysql> INSERT INTO animals (id,name) VALUES(100,'rabbit'); 
Query OK, 1 row affected (0.00 sec)  
mysql> select * from animals; 
+-----+-----------+ 
| id  | name      | 
+-----+-----------+ 
|   1 | fish       | 
|   2 | cat       | 
|   3 | penguin   | 
| 100 | rabbit    | 
+-----+-----------+ 
9 rows in set (0.00 sec)

未提交的事务或回滚的事务，会导致AUTO_INCREMENT增长，但回滚后不会下降。后续如果再次插入数据就会导致数据中的自增字段发生跳变。

mysql> show create table auto_test1; 
+------------+----------------------------------------+ 
| Table      | Create Table                           | 
+------------+----------------------------------------+ 
| auto_test1 | CREATE TABLE `auto_test1` (   
`id` int NOT NULL AUTO_INCREMENT,   
PRIMARY KEY (`id`) 
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 | 
+------------+----------------------------------------+ 
1 row in set (0.00 sec)  
mysql> select * from auto_test1; 
+----+ 
| id | 
+----+ 
|  1 | 
|  2 | 
|  3 | 
+----+ 
mysql> begin; 
Query OK, 0 rows affected (0.02 sec)  
mysql> insert into auto_test1 values (0),(0),(0); 
Query OK, 3 rows affected (0.00 sec) 
Records: 3  Duplicates: 0  Warnings: 0  
mysql> select * from auto_test1; 
+----+ 
| id | 
+----+ 
|  1 | 
|  2 | 
|  3 | 
|  4 | 
|  5 | 
|  6 | 
+----+ 
6 rows in set (0.00 sec)  
mysql> show create table auto_test1; 
+------------+----------------------------------------+ 
| Table      | Create Table                           | 
+------------+----------------------------------------+ 
| auto_test1 | 
CREATE TABLE `auto_test1` (   
`id` int NOT NULL AUTO_INCREMENT,   
PRIMARY KEY (`id`) 
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 | 
+------------+----------------------------------------+ 
1 row in set (0.00 sec)  
mysql> rollback; 
Query OK, 0 rows affected (0.05 sec)  
mysql> select * from auto_test1; 
+----+ 
| id | 
+----+ 
|  1 | 
|  2 | 
|  3 | 
+----+ 
3 rows in set (0.00 sec)  
mysql> show create table auto_test1; 
+------------+----------------------------------------+ 
| Table      | Create Table                           | 
+------------+----------------------------------------+ 
| auto_test1 | CREATE TABLE `auto_test1` (   
`id` int NOT NULL AUTO_INCREMENT,   
PRIMARY KEY (`id`) 
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 | 
+------------+----------------------------------------+  
mysql> insert into auto_test1 values (0),(0),(0); 
Query OK, 3 rows affected (0.01 sec) 
Records: 3  Duplicates: 0  Warnings: 0  
mysql> select * from auto_test1; 
+----+ 
| id | 
+----+ 
|  1 | 
|  2 | 
|  3 | 
|  7 | 
|  8 | 
|  9 | 
+----+ 
6 rows in set (0.00 sec)  
mysql> show create table auto_test1; 
+------------+-----------------------------------------+ 
| Table      | Create Table                            | 
+------------+-----------------------------------------+ 
| auto_test1 | CREATE TABLE `auto_test1` (   
`id` int NOT NULL AUTO_INCREMENT,   
PRIMARY KEY (`id`) 
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 | 
+------------+-----------------------------------------+

数据插入后，AUTO_INCREMENT变化，然后删除对应的数据行，AUTO_INCREMENT不会下降，后续如果再次插入数据就会导致数据中的自增字段发生跳变。

mysql> show create table auto_test1; 
+------------+----------------------------------------+ 
| Table      | Create Table                           | 
+------------+----------------------------------------+ 
| auto_test1 | CREATE TABLE `auto_test1` (   
`id` int NOT NULL AUTO_INCREMENT,   
PRIMARY KEY (`id`) 
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 | 
+------------+----------------------------------------+ 
1 row in set (0.00 sec)  
mysql> select * from auto_test1; 
+----+ 
| id | 
+----+ 
|  1 | 
|  2 | 
|  3 | 
+----+ 
mysql> insert into auto_test1 values (0),(0),(0); 
Query OK, 3 rows affected (0.00 sec) 
Records: 3  Duplicates: 0  Warnings: 0  
mysql> select * from auto_test1; 
+----+ 
| id | 
+----+ 
|  1 | 
|  2 | 
|  3 | 
|  4 | 
|  5 | 
|  6 | 
+----+ 
6 rows in set (0.00 sec)  
mysql> show create table auto_test1; 
+------------+----------------------------------------+ 
| Table      | Create Table                           | 
+------------+----------------------------------------+ 
| auto_test1 | CREATE TABLE `auto_test1` (   
`id` int NOT NULL AUTO_INCREMENT,   
PRIMARY KEY (`id`) 
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 | 
+------------+----------------------------------------+ 
1 row in set (0.00 sec)  
mysql> delete from auto_test1 where id>3;  
mysql> select * from auto_test1; 
+----+ 
| id | 
+----+ 
|  1 | 
|  2 | 
|  3 | 
+----+ 
3 rows in set (0.00 sec)  
mysql> show create table auto_test1; 
+------------+----------------------------------------+ 
| Table      | Create Table                           | 
+------------+----------------------------------------+ 
| auto_test1 | CREATE TABLE `auto_test1` (   
`id` int NOT NULL AUTO_INCREMENT,   
PRIMARY KEY (`id`) 
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 | 
+------------+----------------------------------------+  
mysql> insert into auto_test1 values (0),(0),(0); 
Query OK, 3 rows affected (0.01 sec) 
Records: 3  Duplicates: 0  Warnings: 0  
mysql> select * from auto_test1; 
+----+ 
| id | 
+----+ 
|  1 | 
|  2 | 
|  3 | 
|  7 | 
|  8 | 
|  9 | 
+----+ 
6 rows in set (0.00 sec)  
mysql> show create table auto_test1; 
+------------+-----------------------------------------+ 
| Table      | Create Table                            | 
+------------+-----------------------------------------+ 
| auto_test1 | CREATE TABLE `auto_test1` (   
`id` int NOT NULL AUTO_INCREMENT,   
PRIMARY KEY (`id`) 
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 | 
+------------+-----------------------------------------+

因为一些原因（比如唯一键冲突），使得插入数据最终未成功的，有可能导致AUTO_INCREMENT跳变。

mysql> create table auto_test7(`id` int NOT NULL AUTO_INCREMENT, cred_id int UNIQUE, PRIMARY KEY (`id`));
Query OK, 0 rows affected (0.64 sec) 
mysql> insert into auto_test7 values(null, 1);
Query OK, 1 row affected (0.03 sec) 
mysql> show create table auto_test7;
+------------+-------------------------------+
| Table      | Create Table                  |
+------------+-------------------------------+
| auto_test7 | CREATE TABLE `auto_test7` (  `id` int NOT NULL AUTO_INCREMENT,  `cred_id` int DEFAULT NULL,  PRIMARY KEY (`id`),  UNIQUE KEY `cred_id` (`cred_id`)) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |
+------------+--------------------------------------------------------------+
1 row in set (0.00 sec) 
mysql> insert into auto_test7 values(null, 1);
ERROR 1062 (23000): Duplicate entry '1' for key 'auto_test7.cred_id'
mysql> show create table auto_test7;
+------------+--------------------------------------------------------------+
| Table      | Create Table                                                 |
+------------+--------------------------------------------------------------+
| auto_test7 | CREATE TABLE `auto_test7` (  `id` int NOT NULL AUTO_INCREMENT,  `cred_id` int DEFAULT NULL,  PRIMARY KEY (`id`),  UNIQUE KEY `cred_id` (`cred_id`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |
+------------+---------------------------------------------------------------+

批量插入数据时（如insert...select、load file等），自增键的申请是分批申请的，每批申请2的n次方个序号，用完继续申请，没用完也不会退回，所以可能会导致AUTO_INCREMENT跳变。

mysql> create table auto_test5_tmp(id tinyint not null AUTO_INCREMENT, name varchar(8), PRIMARY KEY (`id`));
Query OK, 0 rows affected (0.08 sec) 
mysql> select * from auto_test5;
+----+------+
| id | name |
+----+------+
|  1 | A    |
|  2 | B    |
|  3 | C    |
|  4 | X    |
|  5 | Y    |
|  6 | Z    |
|  8 | A    |
|  9 | B    |
| 10 | C    |
| 11 | X    |
| 12 | Y    |
| 13 | Z    |
+----+------+
12 rows in set (0.00 sec) 
mysql> insert into auto_test5_tmp select 0,name from auto_test5;
Query OK, 12 rows affected (0.01 sec)
Records: 12  Duplicates: 0  Warnings: 0 
mysql> select * from auto_test5_tmp;
+----+------+
| id | name |
+----+------+
|  1 | A    |
|  2 | B    |
|  3 | C    |
|  4 | X    |
|  5 | Y    |
|  6 | Z    |
|  7 | A    |
|  8 | B    |
|  9 | C    |
| 10 | X    |
| 11 | Y    |
| 12 | Z    |
+----+------+
12 rows in set (0.00 sec) 
mysql> show create table auto_test5_tmp;
+----------------+-------------------------------------------------------+
| Table          | Create Table                                          |
+----------------+-------------------------------------------------------+
| auto_test5_tmp | CREATE TABLE `auto_test5_tmp` (  `id` tinyint NOT NULL AUTO_INCREMENT,  `name` varchar(8) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 |
+----------------+-------------------------------------------------------+

父主题： 其他使用问题

上一篇：表的自增AUTO_INCREMENT超过数据中该字段的最大值加1

下一篇：修改表的自增AUTO_INCREMENT值

意见反馈

文档内容是否对您有帮助？

有帮助没帮助

提供反馈

提交成功！非常感谢您的反馈，我们会继续努力做到更好！您可在我的云声建议查看反馈及问题处理状态。

系统繁忙，请稍后重试

在使用文档中是否遇到以下问题

内容与产品页面不一致

内容不易理解

缺失示例代码

步骤不可操作

搜不到想要的内容

缺少最佳实践

意见反馈（选填）

0/500

请至少选择一项反馈信息并填写问题反馈

字符长度不能超过500

直接提交取消

如您有其它疑问，您也可以通过华为云社区问答频道来与我们联系探讨

智能客服提问云社区提问

自增字段值跳变的原因

相关文档

意见反馈

文档内容是否对您有帮助？

7*24

备案

专业服务

退订

建议反馈

售前咨询热线