MySQL中对于表上ID自增列可以在创建表的时候来指定列上的auto_increment属性；等同于SQL server中的identity属性；Oracle则是通过Sequence方式来实现。在MySQL中，系统变量auto_increment_increment，auto_increment_offset 影响自增列的值及其变化规则。本文主要描述这两个系统变量的相关用法。

1、auto_increment_increment与auto_increment_offset作用

auto_increment_increment控制列中的值的增量值，也就是步长。auto_increment_offset确定AUTO_INCREMENT列值的起点，也就是初始值。变量范围：可以在全局以及session级别设置这2个变量--当前系统环境root@localhost[(none)]> show variables like ‘version‘;+---------------+------------+| Variable_name | Value      |+---------------+------------+| version       | 5.5.39-log |+---------------+------------+root@localhost[mysql]> create database tempdb;root@localhost[mysql]> use tempdb;--查看变量auto_increment_increment与auto_increment_offsetroot@localhost[tempdb]> show variables like ‘%auto_incre%‘;+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 1     || auto_increment_offset    | 1     |+--------------------------+-------+

2、演示auto_increment_increment与auto_increment_offset

--创建演示表，使用auto_increment子句root@localhost[tempdb]> create table t1(id int not null auto_increment primary key, col varchar(20));--插入记录root@localhost[tempdb]> insert into t1(col) values(‘robin‘),(‘fred‘),(‘jack‘),(‘james‘);--下面可以看到id列起始值为1，增量为1root@localhost[tempdb]> select * from t1;+----+-------+| id | col   |+----+-------+|  1 | robin ||  2 | fred  ||  3 | jack  ||  4 | james |+----+-------+--设置步长为5root@localhost[tempdb]> set session auto_increment_increment=5;root@localhost[tempdb]> show variables like ‘%auto_incre%‘;+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 5     || auto_increment_offset    | 1     |+--------------------------+-------+--清空表t1root@localhost[tempdb]> truncate table t1;--再次插入记录root@localhost[tempdb]> insert into t1(col) values(‘robin‘),(‘fred‘),(‘jack‘),(‘james‘);--如下查询可以看到步长以5位基数发生变化root@localhost[tempdb]> select * from t1;+----+-------+| id | col   |+----+-------+|  1 | robin ||  6 | fred  || 11 | jack  || 16 | james |+----+-------+--设置初始值为5root@localhost[tempdb]> set session auto_increment_offset=5;root@localhost[tempdb]> show variables like ‘%auto_incre%‘;+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 5     || auto_increment_offset    | 5     |+--------------------------+-------+root@localhost[tempdb]> truncate table t1;root@localhost[tempdb]> insert into t1(col) values(‘robin‘),(‘fred‘),(‘jack‘),(‘james‘);--下面是新的结果root@localhost[tempdb]> select * from t1;+----+-------+| id | col   |+----+-------+|  5 | robin || 10 | fred  || 15 | jack  || 20 | james |+----+-------+

3、auto_increment_increment与auto_increment_offset取值范围

--将变量auto_increment_increment设置为0root@localhost[tempdb]> set session auto_increment_increment=0;--实际值变成了1root@localhost[tempdb]> show variables like ‘%auto_increment%‘;+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 1     || auto_increment_offset    | 5     |+--------------------------+-------+--同样将auto_increment_offset设置为0root@localhost[tempdb]> set session auto_increment_offset=0;--实际值也变成了1root@localhost[tempdb]> show variables like ‘%auto_increment%‘;+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 1     || auto_increment_offset    | 1     |+--------------------------+-------+--下面尝试将2个变量设置为大于65535root@localhost[tempdb]> set session auto_increment_increment=65537;root@localhost[tempdb]> set session auto_increment_offset=65537;--其实际的值都变成了65535root@localhost[tempdb]> show variables like ‘%auto_increment%‘;+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 65535 || auto_increment_offset    | 65535 |+--------------------------+-------+--尝试为2个变量设置为负值root@localhost[tempdb]> set session auto_increment_offset=-2;root@localhost[tempdb]> set session auto_increment_increment=-5;--下面的查询可以看出全部恢复到缺省值1root@localhost[tempdb]> show variables like ‘%auto_increment%‘;+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 1     || auto_increment_offset    | 1     |+--------------------------+-------+由上可以看出2个变量只能设置为1至65535之间的整数值。所有非正整数全部会置为缺省值1，大于65535的值会被自动置为65535。

4、全局与session级别的设置

--查看全局范围这2个变量的值root@localhost[tempdb]> show global variables like ‘%auto_increment%‘;+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 1     || auto_increment_offset    | 1     |+--------------------------+-------+--下面分别设置session基本的值root@localhost[tempdb]> set session auto_increment_increment=5;root@localhost[tempdb]> set session auto_increment_offset=10;--查看session级别的值root@localhost[tempdb]> show session variables like ‘%auto_increment%‘;+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 5     || auto_increment_offset    | 10    |+--------------------------+-------+--查看全局级别的值root@localhost[tempdb]> show global variables like ‘%auto_increment%‘;+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 1     || auto_increment_offset    | 1     |+--------------------------+-------+--设置全局级别的值root@localhost[tempdb]> set global auto_increment_increment=2;root@localhost[tempdb]> set global auto_increment_offset=3;root@localhost[tempdb]> show global variables like ‘%auto_increment%‘;+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 2     || auto_increment_offset    | 3     |+--------------------------+-------+

5、已有auto_increment列值任一变量变化的情形

root@localhost[tempdb]> truncate table t1;root@localhost[tempdb]> show variables like ‘%auto_increment%‘;+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 1     || auto_increment_offset    | 1     |+--------------------------+-------+root@localhost[tempdb]> insert into t1(col) values(‘robin‘),(‘fred‘),(‘jack‘);          root@localhost[tempdb]> select * from t1;+----+-------+| id | col   |+----+-------+|  1 | robin ||  2 | fred  ||  3 | jack  |+----+-------+root@localhost[tempdb]> set session auto_increment_increment=5;root@localhost[tempdb]> show variables like ‘%auto_increment%‘;+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 5     || auto_increment_offset    | 1     |+--------------------------+-------+--Author: Leshami--Blog  : http://blog.csdn.net/leshamiroot@localhost[tempdb]> insert into t1(col) values(‘david‘),(‘tim‘),(‘jerry‘);root@localhost[tempdb]> select * from t1;+----+-------+| id | col   |+----+-------+|  1 | robin ||  2 | fred  ||  3 | jack  ||  6 | david || 11 | tim   || 16 | jerry |+----+-------+New_value = http://www.mamicode.com/auto_increment_offset+ N * auto_increment_increment>
MySQL auto_increment_increment，auto_increment_offset 用法