auto_increment_increment 和 auto_increment_offset参数用在主主复制中,用于控制AUTO_INCREMENT字段的操作,在不同节点使用不同的生成规则,以避免生成的序列相同而产生冲突。
这两个参数可以分别设置全局和会话的变量,每个参数的值的范围是1~65535。将这两个参数设置为0会导致实际上将这两个参数的值设为1。
auto_increment_increment参数控制AUTO_INCREMENT字段值的间隔数
mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.00 sec)
mysql> CREATE TABLE autoinc1
-> (col int not null auto_increment primary key)
-> ;
Query OK, 0 rows affected (0.12 sec)
mysql> select @@auto_increment_increment;
+----------------------------+
| @@auto_increment_increment |
+----------------------------+
| 1 |
+----------------------------+
1 row in set (0.00 sec)
mysql> set @@auto_increment_increment=10;
Query OK, 0 rows affected (0.06 sec)
mysql> select @@auto_increment_increment;
+----------------------------+
| @@auto_increment_increment |
+----------------------------+
| 10 |
+----------------------------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 10 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.00 sec)
mysql> INSERT INTO autoinc1 VALUES(null),(null),(null);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT col FROM autoinc1;
+-----+
| col |
+-----+
| 1 |
| 11 |
| 21 |
+-----+
3 rows in set (0.00 sec)
auto_increment_offset 参数决定AUTO_INCREMENT字段的起始值
mysql> set @@auto_increment_offset=5;
Query OK, 0 rows affected (0.00 sec)
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> CREATE TABLE autoinc2
-> (col int not null auto_increment primary key);
Query OK, 0 rows affected (0.64 sec)
mysql> INSERT INTO autoinc2 VALUES (null),(null),(null);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT col FROM autoinc2;
+-----+
| col |
+-----+
| 5 |
| 15 |
| 25 |
+-----+
3 rows in set (0.00 sec)
auto_increment_increment参数控制AUTO_INCREMENT字段值的间隔数
mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.00 sec)
mysql> CREATE TABLE autoinc1
-> (col int not null auto_increment primary key)
-> ;
Query OK, 0 rows affected (0.12 sec)
mysql> select @@auto_increment_increment;
+----------------------------+
| @@auto_increment_increment |
+----------------------------+
| 1 |
+----------------------------+
1 row in set (0.00 sec)
mysql> set @@auto_increment_increment=10;
Query OK, 0 rows affected (0.06 sec)
mysql> select @@auto_increment_increment;
+----------------------------+
| @@auto_increment_increment |
+----------------------------+
| 10 |
+----------------------------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 10 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.00 sec)
mysql> INSERT INTO autoinc1 VALUES(null),(null),(null);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT col FROM autoinc1;
+-----+
| col |
+-----+
| 1 |
| 11 |
| 21 |
+-----+
3 rows in set (0.00 sec)
auto_increment_offset 参数决定AUTO_INCREMENT字段的起始值
mysql> set @@auto_increment_offset=5;
Query OK, 0 rows affected (0.00 sec)
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> CREATE TABLE autoinc2
-> (col int not null auto_increment primary key);
Query OK, 0 rows affected (0.64 sec)
mysql> INSERT INTO autoinc2 VALUES (null),(null),(null);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT col FROM autoinc2;
+-----+
| col |
+-----+
| 5 |
| 15 |
| 25 |
+-----+
3 rows in set (0.00 sec)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26506993/viewspace-2098997/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26506993/viewspace-2098997/