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

父主题： 其他使用问题

同意关联代理商云淘科技，购买华为云产品更优惠（QQ 78315851）

内容没看懂？ 不太想学习？想快速解决？ 有偿解决： 联系专家