基于 MySQL 5.6
背景
最近在玩 MySQL 双主复制架构,表里的主键使用自增ID,为了避免两台主库生成的主键冲突,遂两台主库分别配置如下:
server 1
的 my.cnf
:
auto_increment_increment = 2
auto_increment_offset = 1
server 2
的 my.cnf
:
auto_increment_increment = 2
auto_increment_offset = 2
按照这个配置,本以为 server 1
和 server 2
生成序列分别是 1
,3
,5
··· 和 2
, 4
, 6
··· 这样的序列,但事实上并不完全是这样,下面来做个试验。
重现
基于以上配置,在 server 1
上建表:
CREATE TABLE `test`.`table_name` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4;
因为我配置了双主同步,所以此表将被同步到 server 2
上。
执行如下添加语句初始化数据:
INSERT INTO `test`.`table_name` (`name`) VALUES ('myname0');
INSERT INTO `test`.`table_name` (`name`) VALUES ('myname1');
INSERT INTO `test`.`table_name` (`name`) VALUES ('myname2');
数据将被同步到 server 2
上。
在 server 1
上查询此表,可以看到刚才插入的数据:
id | name |
---|---|
1 | myname0 |
3 | myname1 |
5 | myname2 |
结果如我们所料,id
列呈奇数自增。在 server 2
上查询的结果和上面一样。
接着,在 server 2
上向此表再添加几条数据:
INSERT INTO `test`.`table_name` (`name`) VALUES ('myname3');
INSERT INTO `test`.`table_name` (`name`) VALUES ('myname4');
INSERT INTO `test`.`table_name` (`name`) VALUES ('myname5');
同样数据,将被同步到 server 1
上。
查询此表,得到的结果:
id | name |
---|---|
1 | myname0 |
3 | myname1 |
5 | myname2 |
6 | myname3 |
8 | myname4 |
10 | myname5 |
问题出来了,server 2
分配的序列并不像我们之前期望的那样,从 2
开始的连续偶数,而是跳过 2
和 4
,直接从 6
开始。
解决
研究了很久,翻看 MySQL 官网文档,没有提到会是这样子,Goole 了两天,也都说,自增ID分别是 1
、2
、3
和 2
、 4
、 6
,并没有对此情况做明确说明。直到我看到 这位大神的回答。
具体的我就不重复了。
总结
总结一下:
1. AUTO_INCREMENT
所在的列,必须为整数型数据列
2. AUTO_INCREMENT
所在的列,不能为空
3. AUTO_INCREMENT
所在的列,必须有唯一索引
4. AUTO_INCREMENT
所在的列,值必须大于0
5. AUTO_INCREMENT
所在的列,最大值,受其数据类型及是否为 无符号(Unsigned
) 限制,若使用的为 TINYINT(4)
且 为无符号的,则最大值为 255,若继续插入数据,则该列的值保持最大值不变,
6. AUTO_INCREMENT
所在的列,若向其中插入的值,大于所在表当前的 AUTO_INCREMENT
值,则会更新表 AUTO_INCREMENT
值至 current_max_value
- (current_max_value
- auto_increment_offset
) % auto_increment_increment
+ auto_increment_increment
,即该列的下一个序列值