聚合国内IT技术精华文章,分享IT技术精华,帮助IT从业人士成长

略奇葩的多表跳跃自增需求

2020-03-31 13:01 浏览: 636 次 我要评论(0 条) 字号:


在同一个实例下,如何让多个表有不同自增起始值,且以相同步幅自增?

某天,小明提了个需求,是下面这样的

  1. 有10个结构一样的表
  2. 每个表的主键起始值分别从 1 递增到 10
  3. 每个表的主键自增值每次都跳跃 10(因为总共10个表),也就是说第一个表的主键值是这样的1, 11, 21, 31。

接下来,小明是这么做的:

1、修改auto_increment设置

[root@yejr.me]> show variables like '%auto_increment%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 10    |
| auto_increment_offset    | 1     |
+--------------------------+-------+

2、创建表,并插入数据

[root@yejr.me]> create table m01(
id int primary key auto_increment, name varchar(64));
...
[root@yejr.me]> create table m10(
id int primary key auto_increment, name varchar(64));

[root@yejr.me]> insert into m01(id,name) values(1,'a');
[root@yejr.me]> insert into m01(id,name) values(0,'a');
...
[root@yejr.me]> insert into m10(id,name) values(10,'j');
[root@yejr.me]> insert into m10(id,name) values(0,'j');

3、读取数据检查正确与否

[root@yejr.me]> select * from m01;
+----+------+
| id | name |
+----+------+
|  1 | a    |
| 11 | a    |
+----+------+

[root@yejr.me]> select * from m02;
+----+------+
| id | name |
+----+------+
|  2 | b    |
| 11 | b    |  --本来期望的值是12才对
+----+------+

[root@yejr.me]> select* from m10;
+----+------+
| id | name |
+----+------+
| 10 | j    |
| 11 | j    |  --本来期望的值是20才对
+----+------+

并不符合预期,那到底怎么做才对呢?

看来,小明并没有正确设置关于自增的2个选项。

我们先来看下这2个选项的作用描述:

  • auto_increment_increment,控制每次自增的步幅,也就是每次自增长的幅度
  • auto_increment_offset,设置自增列的起始值

事实上,这2个选项通常用于双主或环形复制环境,让两个节点分别使用不同的自增值,比如一个是1、3、5、7,另一个是2、4、6、8。

在误操作或不慎在两边都写入数据时,不会发生主键值重复冲突的情况。尤其是在发生故障切换时,原来的主库A可能有部分数据没及时复制到从库B上,当旧主库A恢复后,这些数据再复制到从库B后也不会发生主键值重复冲突的问题。

好了,既然知道了这2个选项的作用,那么正确的做法就该是这样的。

往第1个表里写数据:

[root@yejr.me]> set auto_increment_offset = 1;
[root@yejr.me]> set auto_increment_increment = 10;
[root@yejr.me]> insert into m01(id,name) values(1,'a');
[root@yejr.me]> insert into m01(id,name) values(0,'a');
...
[root@yejr.me]> select * from m01;
+----+------+
| id | name |
+----+------+
|  1 | a    |
| 11 | a    |
| 21 | a    |
+----+------+

往第2个表里写数据:

[root@yejr.me]> set auto_increment_offset = 2; ---注意这里
[root@yejr.me]> set auto_increment_increment = 10;
[root@yejr.me]> insert into m02(id,name) values(2,'b');
[root@yejr.me]> insert into m02(id,name) values(0,'b');
...
[root@yejr.me]> select * from m02;
+----+------+
| id | name |
+----+------+
| 12 | b    |
| 22 | b    |
| 32 | b    |
+----+------+

往第10个表里写数据:

[root@yejr.me]> set auto_increment_offset = 10; ---注意这里
[root@yejr.me]> set auto_increment_increment = 10;
[root@yejr.me]> insert into m10(id,name) values(10,'j');
[root@yejr.me]> insert into m10(id,name) values(0,'j');
...
[root@yejr.me]> select * from m10;
+----+------+
| id | name |
+----+------+
| 10 | c    |
| 20 | c    |
| 30 | c    |
+----+------+

这样就是符合预期的了。

最后小结一下。

  1. 一般只会在双主或环形复制环境下才会特意设置自增偏移量和步幅。
  2. 若要在同一个实例内不同表之间也实现不同偏移量的自增,就需要在针对每个表的那个写入会话中单独调整自增选项设置。

虽然这个案例并不常见,不过也有点意思,就拿出来说说了,就酱。

enjoy mysql。

【全文完】


由叶老师主讲的知数堂「MySQL优化课」第17期已发车,课程从第15期就升级成MySQL 8.0版本了,现在上车刚刚好,扫码开启MySQL 8.0的修行之旅吧。

另外,叶老师在腾讯课堂的短课程《MySQL性能优化》已开课,本课程讲解读几个MySQL性能优化的核心要素:合理利用索引,降低锁影响,提高事务并发度。

下面是报名小程序码



网友评论已有0条评论, 我也要评论

发表评论

*

* (保密)

Ctrl+Enter 快捷回复