Starting Value and Increment of AUTO_INCREMENT

The starting value and increment of AUTO_INCREMENT are determined by the auto_increment_offset and auto_increment_increment parameters.

• auto_increment_offset determines the starting point for the AUTO_INCREMENT column value.
• auto_increment_increment controls the interval between successive column values.
• When the value of auto_increment_offset is greater than that of auto_increment_increment, the value of auto_increment_offset is ignored.

• When the value of auto_increment_offset is less than or equal to that of auto_increment_increment, the value of AUTO_INCREMENT is calculated as auto_increment_offset + N x auto_increment_increment (N indicates the number of inserted data records).

In RDS for MySQL, the values of auto_increment_increment and auto_increment_offset are both 1 by default. You can change them on the RDS console. For details, see Modifying RDS for MySQL Instance Parameters.

Example:

• If both auto_increment_offset and auto_increment_increment are set to 1, the starting value is 1 and the increment is 1.

  mysql> show variables like 'auto_inc%'; 
  +--------------------------+-------+ 
  | Variable_name            | Value | 
  +--------------------------+-------+ 
  | auto_increment_increment | 1     | 
  | auto_increment_offset    | 1     | 
  +--------------------------+-------+ 
  mysql> create table auto_test1(id int NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`)); 
  Query OK, 0 rows affected (0.09 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 DEFAULT CHARSET=utf8 | 
  +------------+------------------------------------------------------------------------------+ 
  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 | 
  +----+ 
  3 rows in set (0.01 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=4 DEFAULT CHARSET=utf8 | 
  +------------+-----------------------------------------------------------------------------------------------+ 
  1 row in set (0.00 sec)

• If auto_increment_increment is set to 2, the increment is 2.

  mysql> set session auto_increment_offset=2; 
  Query OK, 0 rows affected (0.02 sec)  
  mysql> show variables like 'auto_inc%'; 
  +--------------------------+-------+ 
  | Variable_name            | Value | 
  +--------------------------+-------+ 
  | auto_increment_increment | 2     | 
  | auto_increment_offset    | 1     | 
  +--------------------------+-------+ 
  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 | 
  |  6 | 
  |  8 | 
  +----+ 
  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       | 
  +------------+-----------------------------------------------+ 
  1 row in set (0.01 sec)

• If auto_increment_offset is set to 10 and auto_increment_increment is set to 2, the starting value is 2 (because the value of auto_increment_offset is greater than that of auto_increment_increment) and the increment is 2.

  mysql> set session auto_increment_offset=10; 
  mysql> set session auto_increment_increment=2; 
  mysql> show variables like 'auto_inc%'; 
  +--------------------------+-------+ 
  | Variable_name            | Value | 
  +--------------------------+-------+ 
  | auto_increment_increment | 2     | 
  | auto_increment_offset    | 10    | 
  +--------------------------+-------+  
  mysql> create table auto_test2(id int NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`)); Query OK, 0 rows affected (0.08 sec)  
  mysql> show create table auto_test2; 
  +------------+---------------------------------------------------------------------------------------------------------------------------+ 
  | Table      | Create Table                                                                                                              | 
  +------------+---------------------------------------------------------------------------------------------------------------------------+ 
  | auto_test2 | CREATE TABLE `auto_test2` (   `id` int NOT NULL AUTO_INCREMENT,   PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | 
  +------------+---------------------------------------------------------------------------------------------------------------------------+ 
  1 row in set (0.01 sec)  
  mysql> insert into auto_test2 values(0), (0), (0); 
  Query OK, 3 rows affected (0.00 sec) 
  Records: 3  Duplicates: 0  Warnings: 0  
  mysql> select * from auto_test2; 
  +----+ 
  | id | 
  +----+ 
  |  2 | 
  |  4 | 
  |  6 | 
  +----+ 
  3 rows in set (0.01 sec)  
  mysql> show create table auto_test2; 
  +------------+-----------------------------------------------------------------------------------------------+ 
  | Table      | Create Table                                                                                  | 
  +------------+-----------------------------------------------------------------------------------------------+ 
  | auto_test2 | CREATE TABLE `auto_test2` (   
  `id` int NOT NULL AUTO_INCREMENT,   PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 | 
  +------------+-----------------------------------------------------------------------------------------------+

• If auto_increment_offset is set to 5 and auto_increment_increment is set to 10, the starting value is 5 and the increment is 10.

  mysql> set session auto_increment_offset=5; mysql> set session auto_increment_increment=10; 
  mysql>  show variables like 'auto_inc%'; 
  +--------------------------+-------+ 
  | Variable_name            | Value | 
  +--------------------------+-------+ 
  | auto_increment_increment | 10    | 
  | auto_increment_offset    | 5     | 
  +--------------------------+-------+ 
  mysql> create table auto_test3(id int NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`)); 
  mysql> show create table auto_test3; 
  +------------+---------------------------------------------------------------------------------------------------------------------------+ 
  | Table      | Create Table                                                                                                              | 
  +------------+---------------------------------------------------------------------------------------------------------------------------+ 
  | auto_test3 | CREATE TABLE `auto_test3` (   `id` int NOT NULL AUTO_INCREMENT,   PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | 
  +------------+---------------------------------------------------------------------------------------------------------------------------+ 
  mysql> insert into auto_test3 values(0), (0), (0); 
  Query OK, 3 rows affected (0.00 sec) 
  Records: 3  Duplicates: 0  Warnings: 0  
  mysql> select * from auto_test3; 
  +----+ 
  | id | 
  +----+ 
  |  5 | 
  | 15 | 
  | 25 | 
  +----+ 
  mysql> show create table auto_test3; 
  +------------+-----------------------------------------+ 
  | Table      | Create Table                            | 
  +------------+-----------------------------------------+ 
  | auto_test3 | CREATE TABLE `auto_test3` (   
  `id` int NOT NULL AUTO_INCREMENT,   PRIMARY KEY (`id`) 
  ) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8 | 
  +------------+-----------------------------------------+