# 表的自增AUTO_INCREMENT初值与步长 AUTO_INCREMENT的初值与步长由"auto_increment_increment"和"auto_increment_offset"参数决定。 - auto_increment_offset：AUTO_INCREMENT值的初值。 - auto_increment_increment：AUTO_INCREMENT值每次增长的步长。 - 当 auto_increment_offset \> auto_increment_increment 时，实际使用时初值会变为auto_increment_increment。 - 当 auto_increment_offset \<= auto_increment_increment 时，自增值计算方式：值 = auto_increment_offset + N\*auto_increment_increment（N为插入的数据条数） 在RDS for MySQL中"auto_increment_increment"和"auto_increment_offset"参数默认都为1，如需修改请在控制台修改，具体操作请参见[修改RDS for MySQL实例参数](https://support.huaweicloud.com/usermanual-rds-mysql/rds_configuration.html)。 举例： - auto_increment_offset=1，auto_increment_increment=1，那么初值为1，步长为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) ``` - 修改auto_increment_increment=2，步长变为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) ``` - auto_increment_offset=10，auto_increment_increment=2，初值为2（因为auto_increment_offset \> auto_increment_increment），步长为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 | +------------+-----------------------------------------------------------------------------------------------+ ``` - auto_increment_offset=5，auto_increment_increment=10，初值为5，步长为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 | +------------+-----------------------------------------+ ```