表的自增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实例参数。

举例：

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