MySQL auto_increment_increment，auto_increment_offset 用法

ikjhgf

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_offset  
    root@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，增量为1  
    root@localhost[tempdb]> select * from t1;  
    +----+-------+  
    | id | col   |  
    +----+-------+  
    |  1 | robin |  
    |  2 | fred  |  
    |  3 | jack  |  
    |  4 | james |  
    +----+-------+  
      
    --设置步长为5  
    root@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     |  
    +--------------------------+-------+  
      
    --清空表t1  
    root@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 |  
    +----+-------+  
      
    --设置初始值为5  
    root@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设置为0  
    root@localhost[tempdb]> set session auto_increment_increment=0;  
      
    --实际值变成了1  
    root@localhost[tempdb]> show variables like '%auto_increment%';  
    +--------------------------+-------+  
    | Variable_name            | Value |  
    +--------------------------+-------+  
    | auto_increment_increment | 1     |  
    | auto_increment_offset    | 5     |  
    +--------------------------+-------+  
      
    --同样将auto_increment_offset设置为0  
    root@localhost[tempdb]> set session auto_increment_offset=0;  
      
    --实际值也变成了1  
    root@localhost[tempdb]> show variables like '%auto_increment%';  
    +--------------------------+-------+  
    | Variable_name            | Value |  
    +--------------------------+-------+  
    | auto_increment_increment | 1     |  
    | auto_increment_offset    | 1     |  
    +--------------------------+-------+  
      
    --下面尝试将2个变量设置为大于65535  
    root@localhost[tempdb]> set session auto_increment_increment=65537;  
      
    root@localhost[tempdb]> set session auto_increment_offset=65537;  
      
    --其实际的值都变成了65535  
    root@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;  
      
    --下面的查询可以看出全部恢复到缺省值1  
    root@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     |  
    +--------------------------+-------+  
      
  
      
    root@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 = auto_increment_offset+ N * auto_increment_increment  
    New_value1 = 1 + 1 * 5 = 6  
    New_value2 = 1 + 2 * 5 = 11  
      
    --下面是修改auto_increment_offset后的结果  
    root@localhost[tempdb]> set session auto_increment_offset=2;  
      
    root@localhost[tempdb]> insert into t1(col) values('lewis'),('ian');  
      
    root@localhost[tempdb]> select * from t1;  
    +----+-------+  
    | id | col   |  
    +----+-------+  
    |  1 | robin |  
    |  2 | fred  |  
    |  3 | jack  |  
    |  6 | david |  
    | 11 | tim   |  
    | 16 | jerry |  
    | 22 | lewis |  
    | 27 | ian   |  
    +----+-------+  
      
    这个id为22，应该是这样推算来的：max(id)+(new_offset-old_offset)+increment  
    也就是说变化auto_increment_offset后的第一个值为max(id)+(new_offset-old_offset)+increment之后再按步长递增。