更新时间:2023-05-16 GMT+08:00

自增字段值跳变的原因

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

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