深入MySQL--MySQL replace into导致的自增id问题

今天线上遇到一个问题,挺有意思,这里记录一下希望对大家有所帮助。某个表中,只有一条记录,发生高可用切换之后,自增id的值发生了变化,主从的自增id值不一致,导致数据写入报主键冲突的错误。

我们知道,在MySQL中,是支持replace语法的,当你执行replace into的时候,如果该条记录存在,那么replace会删除这条记录,然后重新insert一条新记录。这种操作在主从复制的场景下,可能会带来问题,这里我们简单模拟一下,建表语句如下:

  1. CREATE TABLE `test1` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `age` int(11) DEFAULT NULL,
  4.   PRIMARY KEY (`id`),
  5.   UNIQUE KEY `uni_age` (`age`)
  6. ) ENGINE=InnoDB  DEFAULT CHARSET=utf8
复制代码


可以看到,表中的id是主键,age是唯一索引,我们先插入(2,2)和(3,3)两条数据。

然后,我们先来看主库上

  1. mysql >>select * from test1;
  2. +----+------+
  3. | id | age  |
  4. +----+------+
  5. |  2 |    2 |
  6. |  3 |    3 |
  7. +----+------+
  8. 2 rows in set (0.00 sec)

  9. mysql >>replace into test1 values (6,3);
  10. Query OK, 2 rows affected (0.00 sec)

  11. mysql >>select * from test1;
  12. +----+------+
  13. | id | age  |
  14. +----+------+
  15. |  2 |    2 |
  16. |  6 |    3 |
  17. +----+------+
  18. 2 rows in set (0.00 sec)

  19. mysql >>show create table test1\G
  20. *************************** 1. row ***************************
  21.        Table: test1
  22. Create Table: CREATE TABLE `test1` (
  23.   `id` int(11) NOT NULL AUTO_INCREMENT,
  24.   `age` int(11) DEFAULT NULL,
  25.   PRIMARY KEY (`id`),
  26.   UNIQUE KEY `uni_age` (`age`)
  27. ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8
  28. 1 row in set (0.00 sec)
复制代码


主库上进行replace之后,返回值是2 rows affected,其中之所以返回影响2行,是因为replace into的值是(6,3),而age=3这条记录已经存在,所以会先删除id=3,age=3这条记录,然后插入id=6,age=3这条记录,自增值变为7.

再来看从库上:
游客,如果您要查看本帖隐藏内容请回复
标签: 暂无标签
lee

写了 75 篇文章,拥有财富 290,被 8 人关注

转播转播 分享分享 分享聚知识
回复

使用道具

成为第一个吐槽的人

您需要登录后才可以回帖 登录 | 加入社区

本版积分规则

意见
反馈