MySQL 中 AUTO_INCREMENT 的“坑”

基于 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 ，即该列的下一个序列值