控制AUTO_INCREMENT列的系统变量auto_increment_increment和auto_increment_offset

控制AUTO_INCREMENT列的系统变量 auto_increment_increment和 auto_increment_offset

 

auto_increment_increment 和auto_increment_offset 用于主服务器－主服务器（master-to-master ）复制，并可以用来控制AUTO_INCREMENT 列的操作。两个变量均可以设置为全局或局部变量，并且假定每个值都可以为1 到65,535 之间的整数值。将其中一个变量设置为0 会使该变量为1 。如果试图将这些变量设置为大于65,535 或小于0 的值，则会将该值设置为65,535 。如果向将auto_increment_increment 或auto_increment_off set 设置为非整数值，则会给出错误，并且变量的实际值在这种情况下保持不变。

这两个变量影响AUTO_INCREMENT 列的方式：

o        auto_increment_increment 控制列中的值的增量值。例如:

o                     

  mysql> SHOW VARIABLES LIKE 'auto_inc%';

o                     

  +--------------------------+-------+

o                     

  | Variable_name            | Value |

o                     

  +--------------------------+-------+

o                     

  | auto_increment_increment | 1     |

o                     

  | auto_increment_offset    | 1     |

o                     

  +--------------------------+-------+

o                     

  2 rows in set (0.00 sec)

o                     

 

o                     

  mysql> CREATE TABLE autoinc1 (col INT NOT NULL AUTO_INCREMENT PRIMARY KEY);

o                     

  Query OK, 0 rows affected (0.04 sec)

o                     

 

o                     

  mysql> SET @auto_increment_increment=10;

o                     

  Query OK, 0 rows affected (0.00 sec)

o                     

 

o                     

  mysql> SHOW VARIABLES LIKE 'auto_inc%';

o                     

  +--------------------------+-------+

o                     

  | Variable_name            | Value |

o                     

  +--------------------------+-------+

o                     

  | auto_increment_increment | 10    |

o                     

  | auto_increment_offset    | 1     |

o                     

  +--------------------------+-------+

o                     

  2 rows in set (0.01 sec)

o                     

 

o                     

  mysql> INSERT INTO autoinc1 VALUES (NULL), (NULL), (NULL), (NULL);

o                     

  Query OK, 4 rows affected (0.00 sec)

o                     

  Records: 4  Duplicates: 0  Warnings: 0

o                     

 

o                     

  mysql> SELECT col FROM autoinc1;

o                     

  +-----+

o                     

  | col |

o                     

  +-----+

o                     

  |   1 |

o                     

  |  11 |

o                     

  |  21 |

o                     

  |  31 |

o                     

  +-----+

o                     

  4 rows in set (0.00 sec)

( 注明如何使用SHOW VARIABLES 来获取这些变量的当前值）。

o        auto_increment_offset 确定AUTO_INCREMENT 列值的起点。假定在与前面的例子的相同的会话中执行下面的命令：

o                     

  mysql> SET @auto_increment_offset=5;

o                     

  Query OK, 0 rows affected (0.00 sec)

o                     

 

o                     

  mysql> SHOW VARIABLES LIKE 'auto_inc%';

o                     

  +--------------------------+-------+

o                     

  | Variable_name            | Value |

o                     

  +--------------------------+-------+

o                     

  | auto_increment_increment | 10    |

o                     

  | auto_increment_offset    | 5     |

o                     

  +--------------------------+-------+

o                     

  2 rows in set (0.00 sec)

o                     

 

o                     

  mysql> CREATE TABLE autoinc2 (col INT NOT NULL AUTO_INCREMENT PRIMARY KEY);

o                     

  Query OK, 0 rows affected (0.06 sec)

o                     

 

o                     

  mysql> INSERT INTO autoinc2 VALUES (NULL), (NULL), (NULL), (NULL);

o                     

  Query OK, 4 rows affected (0.00 sec)

o                     

  Records: 4  Duplicates: 0  Warnings: 0

o                     

 

o                     

  mysql> SELECT col FROM autoinc2;

o                     

  +-----+

o                     

  | col |

o                     

  +-----+

o                     

  |   5 |

o                     

  |  15 |

o                     

  |  25 |

o                     

  |  35 |

o                     

  +-----+

o                     

  4 rows in set (0.02 sec)

o                     
 

如果auto_increment_off set 的值大于auto_increment_increment 的值，则auto_increment_offset 的值被忽略。

如果其中一个或两个变量被更改了，然后更改插入到包含AUTO_INCREMENT 列的表中的新行，结果可能看上去有问题，由于计算AUTO_INCREMENT 系列值时没有考虑列内已经存在的值，并且插入的下一个值是列内最小的值，大于AUTO_INCREMENT 列内已有的最大值。换句话说，数值的计算方法为：

auto_increment_offset+ N * auto_increment_increment

其中N 为系列内的正整数值[1,2,3,...] 。例如：

mysql> 
SHOW VARIABLES LIKE 'auto_inc%';

+--------------------------+-------+

| Variable_name            | Value |

+--------------------------+-------+

| auto_increment_increment | 10    |

| auto_increment_offset    | 5     |

+--------------------------+-------+

2 rows in set (0.00 sec)

mysql> 
SELECT col FROM autoinc1;

+-----+

| col |

+-----+

|   1 |

|  11 |

|  21 |

|  31 |

+-----+

4 rows in set (0.00 sec)

mysql> 
INSERT INTO autoinc1 VALUES (NULL), (NULL), (NULL), (NULL);

Query OK, 4 rows affected (0.00 sec)

Records: 4  Duplicates: 0  Warnings: 0

mysql> 
SELECT col FROM autoinc1;

+-----+

| col |

+-----+

|   1 |

|  11 |

|  21 |

|  31 |

|  35 |

|  45 |

|  55 |

|  65 |

+-----+

8 rows in set (0.00 sec)

auto_increment_increment 和auto_increment_offset 所示的值可以生成系列5 + N * 10, 即，[5,15,25,35,45,...] 。在INSERT 前col 列内最大的值为31, AUTO_INCREMENT 数列的下一个值为35, 因此col 中插入的值从该点开始，结果如SELECT 查询所示。

一定要记住不可能将这两个变量的结果限制到一个表中，因此不会替代其它数据库管理系统提供的序列；这些变量控制MySQL 服务器上all 表AUTO_INCREMENT 列的所有行为。如果某个变量设为全局变量，则只有通过局部设置将全局值更改和覆盖后或mysqld 重启后其作用方可改变；如果局部设置，则新值影响所有表的AUTO_INCREMENT 列，在这个会话期间当前用户在这些表中插入了新行，除非在会话期间更改了这些值。

 

auto_increment_increment 的 默认值为1 。auto_increment_offset 变量的默认值为1 。

 

 

################################

注：可以在mysql配置文件my.cnf设置上述两个系统变量的值。

在my.cnf中添加如下设置:
　　[mysqld]
　　#这2个设置是为了防 止2台互相复制的服务器产生关键字段的冲突用的，
　　#如果他们用不同的偏移， 就可以实现一台服务器按照1,3,5,7..增加
　　#另一台服务器按照 个2,4,6,8..增加
　　#如果服务器的复制是单向的，就不需要这个2个设置项
　　auto_increment_increment = 2 #自动增加的字段每次步进是2
　　auto_increment_offset = 1 #自动增加的字段的初始值是1