MySQL 5.7 自增字段相关参数说明

素颜马尾好姑娘i 2024-04-17 23:56 76阅读 0赞

**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)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2098997/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2098997/