Value and Increment of AUTO_INCREMENT
Background Description:
The initial value and increment of AUTO_INCREMENT are determined by auto_increment_increment and auto_increment_offset.
1.auto_increment_offset: Initial value of AUTO_INCREMENT.
2.auto_increment_increment: Increment of AUTO_INCREMENT each time
3. When auto_increment_offset is greater than auto_increment_increment, the initial value becomes auto_increment_increment during use.
4. When auto_increment_offset is less than or equal to auto_increment_increment, the value of AUTO_INCREMENT is calculated as follows: Value = auto_increment_offset + N × auto_increment_increment (N is the number of data inserted).
Actual Operation:
Both auto_increment_increment and auto_increment_offset are default to 1 in a highly available relational MySQL.
The operation example is as follows:
1. When auto_increment_offset is 1 and auto_increment_increment is 1, the initial 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 | +--------------------------+-------+ 2 rows in set (0.00 sec) mysql> create table tiayiyun_test(uid int NOT NULL AUTO_INCREMENT, PRIMARY KEY (`uid`)); Query OK, 0 rows affected (0.01 sec) mysql> show create table tiayiyun_test; +---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+ | tiayiyun_test | CREATE TABLE `tiayiyun_test` ( `uid` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`uid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin | +---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> insert into tiayiyun_test values(0), (0), (0); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from tiayiyun_test; +-----+ | uid | +-----+ | 1 | | 2 | | 3 | +-----+ 3 rows in set (0.00 sec) mysql> show create table tiayiyun_test; +---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tiayiyun_test | CREATE TABLE `tiayiyun_test` ( `uid` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`uid`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin | +---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) |
2. When auto_increment_increment is set to 2, the increment becomes 2.
mysql> set session auto_increment_offset=2; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'auto_inc%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 1 | | auto_increment_offset | 2 | +--------------------------+-------+ 2 rows in set (0.00 sec) mysql> insert into tiayiyun_test values(0), (0), (0); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from tiayiyun_test; +-----+ | uid | +-----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | +-----+ 6 rows in set (0.00 sec) mysql> show create table tiayiyun_test; +---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tiayiyun_test | CREATE TABLE `tiayiyun_test` ( `uid` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`uid`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin | +---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) |
3. When auto_increment_offset is 10 and auto_increment_increment is 2, the initial value is 2 (because auto_increment_offset is greater than auto_increment_increment) and the increment is 2.
mysql> set session auto_increment_offset=10; Query OK, 0 rows affected (0.00 sec) mysql> set session auto_increment_increment=2; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'auto_inc%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 2 | | auto_increment_offset | 10 | +--------------------------+-------+ 2 rows in set (0.00 sec) mysql> create table tianyiyun_test(uid int NOT NULL AUTO_INCREMENT, PRIMARY KEY (`uid`)); Query OK, 0 rows affected (0.01 sec) mysql> show create table tianyiyun_test; +----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tianyiyun_test | CREATE TABLE `tianyiyun_test` ( `uid` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`uid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin | +----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> insert into tianyiyun_test values(0), (0), (0); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from tianyiyun_test; +-----+ | uid | +-----+ | 2 | | 4 | | 6 | +-----+ 3 rows in set (0.00 sec) mysql> show create table tianyiyun_test; +----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tianyiyun_test | CREATE TABLE `tianyiyun_test` ( `uid` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`uid`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin | +----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) |
4. When auto_increment_offset is 5 and auto_increment_increment is 10, the initial value is 5 and the increment is 10.
mysql> set session auto_increment_offset=5; Query OK, 0 rows affected (0.00 sec) mysql> set session auto_increment_increment=10; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'auto_inc%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 10 | | auto_increment_offset | 5 | +--------------------------+-------+ 2 rows in set (0.00 sec) mysql> create table tianyiy(uid int NOT NULL AUTO_INCREMENT, PRIMARY KEY (`uid`)); Query OK, 0 rows affected (0.01 sec) mysql> insert into tianyiy values(0), (0), (0); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from tianyiy; +-----+ | uid | +-----+ | 5 | | 15 | | 25 | +-----+ 3 rows in set (0.00 sec) mysql> show create table tianyiy; +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tianyiy | CREATE TABLE `tianyiy` ( `uid` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`uid`) ) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin | +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) |