MySQL中的auto_increment

最新推荐文章于 2024-06-05 22:52:53 发布

耿进

最新推荐文章于 2024-06-05 22:52:53 发布

阅读量6.7k

点赞数 4

分类专栏： auto_increment mgr 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_45925257/article/details/119993605

版权

auto_increment 同时被 2 个专栏收录

1 篇文章 0 订阅

订阅专栏

mgr

1 篇文章 0 订阅

订阅专栏

文章目录

一、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>

耿进

关注

4
点赞
踩
17

收藏

觉得还不错? 一键收藏
0
评论
MySQL中的auto_increment

文章目录一、AUTO_INCREMENT1.1 测试二、auto_increment_increment和auto_increment_offset2.1 解释2.2 测试auto_increment_incrementauto_increment_offset当两者同时更改三.组复制中的group_replication_auto_increment_increment1.解释2.操作2.1 配置2.2 插数据一、AUTO_INCREMENTAUTO_INCREMENT 属性可用于为新行生成唯一标识
复制链接

扫一扫