Auto-Increment Field Value Jump

If the values of the auto-increment field are discontinuous, the possible causes are as follows:

• The increment is not 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 | 
  +----+

• The value of AUTO_INCREMENT is changed.

  mysql> select * from animals; 
  +----+-----------+ 
  | id | name      | 
  +----+-----------+ 
  |  1 | dog       | 
  |  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 | dog       | 
  |   2 | cat       | 
  |   3 | penguin   | 
  | 100 | rabbit    | 
  +-----+-----------+ 
  9 rows in set (0.00 sec)

• The value of the auto-increment field is specified when data is inserted.

  mysql> select * from animals; 
  +----+-----------+ 
  | id | name      | 
  +----+-----------+ 
  |  1 | dog       | 
  |  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 | dog       | 
  |   2 | cat       | 
  |   3 | penguin   | 
  | 100 | rabbit    | 
  +-----+-----------+ 
  9 rows in set (0.00 sec)

• If a transaction is not committed or is rolled back, the value of AUTO_INCREMENT increases but does not decrease after the transaction is rolled back. When data is inserted again, the value of the auto-increment field jumps.

  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 | 
  +------------+-----------------------------------------+

• After data is inserted, the value of AUTO_INCREMENT changes. But when the corresponding data row is deleted, the value of AUTO_INCREMENT does not decrease. When data is inserted again, the value of the auto-increment field jumps.

  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 | 
  +------------+-----------------------------------------+

• If data insertion fails due to some reasons (for example, unique key conflict), the value of AUTO_INCREMENT may jump.

  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 |
  +------------+---------------------------------------------------------------+

• When data is inserted in batches (such as insert...select and load file), the auto-increment key is requested in batches. Two to the power of n sequence numbers are requested in each batch. If the sequence numbers are not used up, the sequence numbers will not be returned. As a result, the value of AUTO_INCREMENT may jump.

  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 |
  +----------------+-------------------------------------------------------+