控制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