auto_increment_increment 和 auto_increment_offset參數(shù)用在主主復(fù)制中，用于控制AUTO_INCREMENT字段的操作，在不同節(jié)點(diǎn)使用不同的生成規(guī)則，以避免生成的序列相同而產(chǎn)生沖突。這兩個(gè)參數(shù)可以分別設(shè)置全局和會(huì)話的變量，每個(gè)參數(shù)的值的范圍是1~65535。將這兩個(gè)參數(shù)設(shè)置為0會(huì)導(dǎo)致實(shí)際上將這兩個(gè)參數(shù)的值設(shè)為1。

auto_increment_increment參數(shù)控制AUTO_INCREMENT字段值的間隔數(shù)

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 參數(shù)決定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)

分享標(biāo)題：MySQL5.7自增字段相關(guān)參數(shù)說明
網(wǎng)站URL：http://weahome.cn/article/iisohi.html