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 Parameters of an RDS for MySQL Instance.

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

Parent topic: Other Issues

Предыдущая статья

Auto-increment Field Values

Следующая статья

AUTO_INCREMENT Value Exceeding the Maximum Value of This Field plus 1

Была ли эта статья полезна?

Поддержка Юридические документы