文档首页/ 云数据库 RDS/ 故障排除/ RDS for MySQL/ 其他使用问题/ 表的自增AUTO_INCREMENT初值与步长
更新时间:2022-09-13 GMT+08:00
分享

表的自增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 | 
    +------------+-----------------------------------------+

相关文档