Help Center/ Relational Database Service/ Troubleshooting/ RDS for MySQL/ Other Issues/ Starting Value and Increment of AUTO_INCREMENT
Updated on 2022-12-05 GMT+08:00

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