MySQL指定自增序列和设置自增列的初始值及步长

简介

auto_increment 指定自增序列
@@auto_increment_offset 指定自增序列的起始值
@@auto_increment_increment 指定自增序列的步长，即每次增加多少。

查看自增相关系统变量：
show variables like '%auto_incre%'

修改自增步长和初始值：
set @@auto_increment_increment=10;
set @@auto_increment_offset=5;

详细说明

示例一：指定自增列

建表时指定id列为自增列：

create table tb_user ( 
    id int not null auto_increment primary key, 
    name varchar (20) not null default 'xxx' 
);

如果表建好后再添加自增列：

#将id列设置为主键自增列
alter table tb_user add id int auto_increment primary key; 

#设置表tb_user自增起始值为1000
alter table tb_user auto_increment=1000;

示例二：修改自增初始值和步长

查看相关系统变量：
show variables like '%auto_incre%'

mysql> show variables like '%auto_incre%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| auto_increment_increment    | 1     |
| auto_increment_offset       | 1     |
+-----------------------------+-------+
2 rows in set (0.03 sec)

默认自增初始值：auto_increment_increment=1
默认步长：auto_increment_offset=1

tb_user默认自增演示：

mysql> create table tb_user ( 
    id int not null auto_increment primary key
);
Query OK, 0 rows affected (0.04 sec)
mysql> INSERT INTO tb_user VALUES (NULL), (NULL), (NULL), (NULL), (NULL);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0
mysql> select * from tb_user;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
5 rows in set (0.10 sec)

mysql> 

修改自增步长auto_increment_increment=10

mysql> set @@auto_increment_increment=10;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%auto_incre%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 10    |
| auto_increment_offset    | 1     |
+--------------------------+-------+
2 rows in set (0.06 sec)

mysql> create table tb_user1 ( 
    id int not null auto_increment primary key
);
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> INSERT INTO tb_user1 VALUES (NULL), (NULL), (NULL), (NULL), (NULL);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0
mysql> select * from tb_user1;
+----+
| id |
+----+
| 11 |
| 21 |
| 31 |
| 41 |
| 51 |
+----+
5 rows in set (0.09 sec)

mysql> 

修改自增初始值auto_increment_offset=5

mysql> set @@auto_increment_offset=5;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%auto_incre%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 10    |
| auto_increment_offset    | 5     |
+--------------------------+-------+
2 rows in set (0.12 sec)
mysql> 
mysql> create table tb_user2 ( 
    id int not null auto_increment primary key
);
mysql> 
mysql> INSERT INTO tb_user2 VALUES (NULL), (NULL), (NULL), (NULL), (NULL);
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0
mysql> 
mysql> select * from tb_user2;
+----+
| id |
+----+
|  5 |
| 15 |
| 25 |
| 35 |
| 45 |
+----+
5 rows in set (0.10 sec)

mysql> 

说明：
When the value of auto_increment_offset is greater than that of auto_increment_increment, the value of auto_increment_offset is ignored.
当auto_increment_offset的值大于auto_increment_increment时，auto_increment_offset的值将被忽略；
即auto_increment_offset必须小于等于auto_increment_increment

官方文档链接：
https://dev.mysql.com/doc/refman/8.0/en/replication-options-source.html#sysvar_auto_increment_offset

（完）