Mysql replace into

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `age` int(11) DEFAULT NULL,
  `msg` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


insert into t (age, msg) values (1,'aaa'),(2,'bbb'),(3,'ccc');

id 为自增主键、age 为 唯一键

尝试在主数据库执行 replace into

replace into t (age, msg) values (1, '111');

因为 age 为 1 已经存在了

所以这里会先执行 delete 然后再执行 insert 、那么则会导致 AUTO_INCREMENT=5 了

但是对于 binlog 来说、仅仅是产生了一条 update 语句。

这样子的话、会导致从数据库那边的 AUTO_INCREMENT 还是 4

主从切换、新主插入失败

我们现在直接看下最新的数据

我们可以看到主数据现在的 AUTO_INCREMENT 的值为 18 、是符合预期的

我们再看看从数据库

可以看到从数据库现在的 AUTO_INCREMENT 为 13 、是比主数据看落后的

现在我们直接将主数据库杀掉、从数据库成为新主、然后尝试 replace into 或者 insert into (不设置 id 的值)

那么会报错主键冲突、插入失败、AUTO_INCREMENT 变为 14

一直直到 AUTO_INCREMENT 的值为 18

如果在这个过程中、我们指定 id 的值、那么 AUTO_INCREMENT 就会变成 id + 1（这个跟正常情况是一样的）

小结一下

• insert 成功 AUTO_INCREMENT 会取当前 insert 的 id 的值 +1
• 正常情况下 insert 失败不会导致 AUTO_INCREMENT 变化
• 新主 AUTO_INCREMENT 落后于表中最大值时、即使是失败也会导致 AUTO_INCREMENT+1

生产上试过主从切换后、replace into 一直插入失败 、最终的结果是 DBA 手动指定自增列的值、该值为当前表中的最大值、才将 AUTO_INCREMENT 的值回归到正常的值。因为新主当时 AUTO_INCREMENT 的值已经落后很多了、插入一直失败、业务受到影响。

当然重启新主也可以重置 AUTO_INCREMENT

replace into 这个 bug 在 Mysql 5.7 中存在、但是在 mysql 8.0 的时候已经被修复了

本文由mdnice多平台发布