MySQL中的auto_increment
松英叡
一、AUTO_INCREMENT
AUTO_INCREMENT 属性可用于为新行生成唯一标识
1.1 测试
#创建表
CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);
#插入数据
INSERT INTO animals (name) VALUES
('dog'),('cat'),('penguin'),
('lax'),('whale'),('ostrich');
#查看
SELECT * FROM animals;
+-----+-----------+
| id | name |
+-----+-----------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
如果你的SQL_MODE没有设置NO_AUTO_VALUE_ON_ZERO SQL 模式。那就算你给自增列插入0或者null值，它还是会自增的，如下：
INSERT INTO animals (id,name) VALUES(0,'groundhog');
INSERT INTO animals (id,name) VALUES(NULL,'squirrel');
SELECT * FROM animals;
+-----+-----------+
| id | name |
+-----+-----------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
| 7 | groundhog |
| 8 | squirrel |
当你将任何其他值插入 AUTO_INCREMENT 列时，该列将设置为该值并重置序列，以便下一个自动生成的值从最大的列值开始。例如：
mysql> INSERT INTO animals (id,name) VALUES(100,'rabbit');
Query OK, 1 row affected (0.02 sec)
mysql> INSERT INTO animals (id,name) VALUES(NULL,'mouse');
Query OK, 1 row affected (0.05 sec)
mysql> SELECT * FROM animals;
+-----+-----------+
| id | name |
+-----+-----------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
| 7 | groundhog |
| 8 | squirrel |
| 100 | rabbit |
| 101 | mouse |
+-----+-----------+
10 rows in set (0.00 sec)
mysql>
要从 1 以外的 AUTO_INCREMENT 值开始，请使用 CREATE TABLE 或 ALTER TABLE 设置该值，如下所示：
mysql> alter table animals auto_increment=100;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> INSERT INTO animals (name) VALUES('gengjin');
Query OK, 1 row affected (0.02 sec)
mysql> SELECT * FROM animals;
+-----+-----------+
| id | name |
+-----+-----------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
| 7 | groundhog |
| 8 | squirrel |
| 100 | rabbit |
| 101 | mouse |
| 201 | gengjin |
+-----+-----------+
11 rows in set (0.00 sec)
二、auto_increment_increment和auto_increment_offset
2.1 解释
auto_increment_increment 和 auto_increment_offset 用于循环（源到源）复制，可用于控制 AUTO_INCREMENT 列的操作。这两个变量都有全局值和会话值，并且每个变量都可以采用 1 到 65535 之间的整数值。将这两个变量中的任何一个的值设置为 0 会导致其值设置为 1。尝试将这两个变量中的任何一个的值设置为大于 65535 或小于 0 的整数会导致其值设置为 65535。尝试将 auto_increment_increment 或 auto_increment_offset 的值设置为非整数值会产生错误，而变量的实际值保持不变。
mysql> show variables like "%increm%";
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| auto_increment_increment | 3 |
| auto_increment_offset | 1 |
| div_precision_increment | 4 |
| group_replication_auto_increment_increment | 3 |
| innodb_autoextend_increment | 64 |
+--------------------------------------------+-------+
5 rows in set (0.01 sec)
mysql>
2.2 测试
auto_increment_increment 和 auto_increment_offset 影响 AUTO_INCREMENT 列行为如下：
auto_increment_increment
mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.01 sec)
mysql> CREATE TABLE autoinc1(col INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.09 sec)
mysql> SET @@auto_increment_increment=10;
Query OK, 0 rows affected (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.01 sec)
mysql> INSERT INTO autoinc1 VALUES (NULL), (NULL), (NULL), (NULL);
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT col FROM autoinc1;
+-----+
| col |
+-----+
| 1 |
| 11 |
| 21 |
| 31 |
+-----+
4 rows in set (0.00 sec)
mysql>
auto_increment_offset
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.01 sec)
mysql>
mysql> CREATE TABLE autoinc2(col INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.08 sec)
mysql>
mysql> INSERT INTO autoinc2 VALUES (NULL), (NULL), (NULL), (NULL);
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql>
mysql> SELECT col FROM autoinc2;
+-----+
| col |
+-----+
| 5 |
| 15 |
| 25 |
| 35 |
+-----+
4 rows in set (0.00 sec)
mysql>
当两者同时更改
当auto_increment_offset 的值大于auto_increment_increment 的值时，忽略auto_increment_offset 的值。
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.02 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT col FROM autoinc1;
+-----+
| col |
+-----+
| 1 |
| 11 |
| 21 |
| 31 |
| 45 |
| 55 |
| 65 |
| 75 |
+-----+
8 rows in set (0.00 sec)
mysql>
三、组复制中的group_replication_auto_increment_increment
1.解释
服务器上启动Group Replication时，将auto_increment_increment的值改为group_replication_auto_increment_increment的值，默认为7，auto_increment_offset的值改为server_ID。
mysql> show variables like "%auto_increm%";
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| auto_increment_increment | 3 |
| auto_increment_offset | 1 |
| group_replication_auto_increment_increment | 3 |
+--------------------------------------------+-------+
3 rows in set (0.00 sec)
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)
mysql>
当组复制停止时，更改将恢复。仅当 auto_increment_increment 和 auto_increment_offset 的默认值都为 1 时，才会进行和恢复这些更改。如果它们的值已经从默认值进行了修改，则 Group Replication 不会更改它们。
从 MySQL 8.0 开始，当 Group Replication 处于单主模式时，系统变量也不会被修改，其中只有一个服务器写入。
2.操作
2.1 配置
##我们现在在搭建一个mgr多主的一个架构，初始数值如下：
所有节点均如下
mysql> show variables like "%auto_increm%";
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| group_replication_auto_increment_increment | 7 |
+--------------------------------------------+-------+
3 rows in set (0.01 sec)
mysql>
将group_replication_auto_increment_increment设置为3
mysql> set global group_replication_auto_increment_increment=3;
Query OK, 0 rows affected (0.00 sec)
mysql>#启动mgr
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> start group_replication;
Query OK, 0 rows affected (3.19 sec)
mysql> SET GLOBAL group_replication_bootstrap_group=off;
Query OK, 0 rows affected (0.00 sec)
#我们将auto_increment_increment 改为3，将auto_increment_offset改为server_id(mgr三节点分别为1，2，3)
PS：理应当启动mgr的时候auto_increment_increment会自动变成group_replication_auto_increment_increment的值，auto_increment_offset变为server_id。
这里加上手动修改方式。
三节点，改法均如下
mysql> set session auto_increment_offset=2;
Query OK, 0 rows affected (0.00 sec)
mysql> set session auto_increment_increment=3;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like "%auto_increm%";
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| auto_increment_increment | 3 |
| auto_increment_offset | 2 |
| group_replication_auto_increment_increment | 3 |
+--------------------------------------------+-------+
3 rows in set (0.00 sec)
mysql>
2.2 插数据
#建表
CREATE TABLE `incre_tb0` (
`increment_id` int NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`stu_id` int NOT NULL COMMENT '学号',
`stu_name` varchar(20) DEFAULT NULL COMMENT '学生姓名',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
PRIMARY KEY (`increment_id`),
UNIQUE KEY `uk_stu_id` (`stu_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试自增主键'；
#1节点插入数据
mysql> insert into incre_tb0 (stu_id,stu_name) values (1001,'gengjin');
Query OK, 1 row affected (0.01 sec)
mysql> insert into incre_tb0 (stu_id,stu_name) values (1002,'kobe');
Query OK, 1 row affected (0.01 sec)
#2节点插入数据
mysql> insert into incre_tb0 (stu_id,stu_name) values (1003,'jordan');
Query OK, 1 row affected (0.00 sec)
mysql> insert into incre_tb0 (stu_id,stu_name) values (1004,'har');
Query OK, 1 row affected (0.00 sec)
#3节点插入数据
mysql> insert into incre_tb0 (stu_id,stu_name) values (1005,'weishao');
Query OK, 1 row affected (0.00 sec)
mysql> insert into incre_tb0 (stu_id,stu_name) values (1006,'gg');
Query OK, 1 row affected (0.00 sec)
#查询
mysql> select * from incre_tb0;
+--------------+--------+----------+---------------------+---------------------+
| increment_id | stu_id | stu_name | create_time | update_time |
+--------------+--------+----------+---------------------+---------------------+
| 1 | 1001 | gengjin | 2021-08-30 15:31:44 | 2021-08-30 15:31:44 |
| 4 | 1002 | kobe | 2021-08-30 15:32:05 | 2021-08-30 15:32:05 |
| 5 | 1003 | jordan | 2021-08-30 15:33:04 | 2021-08-30 15:33:04 |
| 8 | 1004 | har | 2021-08-30 15:33:14 | 2021-08-30 15:33:14 |
| 9 | 1005 | weishao | 2021-08-30 15:33:28 | 2021-08-30 15:33:28 |
| 12 | 1006 | gg | 2021-08-30 15:33:39 | 2021-08-30 15:33:39 |
+--------------+--------+----------+---------------------+---------------------+
6 rows in set (0.00 sec)
mysql>
类似资料：