表的自增auto-j9九游会登录

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