自增字段值跳变的原因-j9九游会登录

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

初值与步长问题，步长不为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

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

自增字段值跳变的原因-j9九游会登录

相关文档

意见反馈

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