资讯

精准传达 • 有效沟通

从品牌网站建设到网络营销策划,从策略到执行的一站式服务

自增列导致主键重复

有记录进行插入时,自增列产生的值就有可能与已有的记录主键冲突,导致出错。首先想办法解决问题,通过人工调大自增列的值,保证大于表内已有的主键即可,调整后,导数据正常

托里网站制作公司哪家好,找创新互联!从网页设计、网站建设、微信开发、APP开发、响应式网站建设等网站项目制作,到程序开发,运营维护。创新互联成立与2013年到现在10年的时间,我们拥有了丰富的建站经验和运维经验,来保证我们的工作的顺利进行。专注于网站建设就选创新互联。

问题发生的前置条件:

       1.MySQL复制基于row模式

       2.innodb表

       3.表含有自增主键,并且含有唯一约束

       4.load data infile 采用replace into语法插入数据【遇到重复唯一约束,直接覆盖】

问题发生的原理:

        1.主库遇到重复unique约束时,进行replace操作;

        2.replace在主库上面实际变化为delete+insert,但binlog记录的是update;

        3.备库重做update动作,更新主键,但由于update动作不会更新自增列值,导致更新后记录值大于自增列值

问题重现实验:

准备工作

Create table test_autoinc(id int auto_increment, c1 int,c2 varchar(100),primary key(id),unique key(c1));

insert into test_autoinc(c1,c2) values(1,'abc');

insert into test_autoinc(c1,c2) values(2,'abc');

insert into test_autoinc(c1,c2) values(3,'abcdd');

insert into test_autoinc(c1,c2) values(4,'abcdd');

insert into test_autoinc(c1,c2) values(5,'abcdd');

1

操作

备注

Master

slave

2

查看自增列值

Show create table test_autoinc\G                             

插入5条记录后,自增列值变为6

CREATE TABLE `test_autoinc` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `c1` int(11) DEFAULT NULL,

  `c2` varchar(100) DEFAULT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `c1` (`c1`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

CREATE TABLE `test_autoinc` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `c1` int(11) DEFAULT NULL,

  `c2` varchar(100) DEFAULT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `c1` (`c1`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

 

3

查看表数据


id | c1   | c2  

---+------+------

 1 |    1 | abc 

 2 |    2 | abc 

 3 |    3 | abcdd

 4 |    4 | abcdd

 5 |    5 | abcdd

id | c1   | c2  

---+------+------

 1 |    1 | abc 

 2 |    2 | abc 

 3 |    3 | abcdd

 4 |    4 | abcdd

 5 |    5 | abcdd

4

查看binlog位置

show master status\G

记录当前binlog位点,

后续可以查看replace动作产生的binlog事件

mysql-bin.000038

59242888


5

replace操作

replace into test_autoinc(c1,c2) values(2,'eeee');

影响两条记录,主库replace=

delete+insert

 

Query OK, 2 rows affected

(0.00 sec)


 

 

6

查看表数据


id | c1   | c2   

---+------+-------

 1 |    1 | abc  

 3 |    3 | abcdd

 4 |    4 | abcdd

 5 |    5 | abcdd

 6 |    2 | eeee 

id | c1   | c2   

---+------+-------

 1 |    1 | abc  

 3 |    3 | abcdd

 4 |    4 | abcdd

 5 |    5 | abcdd

 6 |    2 | eeee 

7

查看binlog事件

show binlog events in 'mysql-bin.000038' from 59242888;

也可以通过mysqlbinlog工具分析日志,查询从库执行的update语句

Pos      | Event_type   

---------+---------------

59242888 | Query        

59242957 | Table_map    

59243013 |Update_rows_v1

59243072 | Xid          


8

查看自增列值

Show create table  test_autoinc\G;

此时master的自增列为7,而slave的自增列为6,与表内最大值相同

CREATE TABLE `test_autoinc` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `c1` int(11) DEFAULT NULL,

  `c2` varchar(100) DEFAULT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `c1` (`c1`)

) ENGINE=InnoDBAUTO_INCREMENT=7

CREATE TABLE `test_autoinc` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `c1` int(11) DEFAULT NULL,

  `c2` varchar(100) DEFAULT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `c1` (`c1`)

) ENGINE=InnoDBAUTO_INCREMENT=6

9手工调大自增主键 Show create table  test_autoinc\G;

手工调大自增id

alter table test_autoinc auto_increment=12;

Show create table  test_autoinc\G;

alter table test_autoinc auto_increment=12;

Show create table  test_autoinc\G;

发现master和slave的自增id一致




当前标题:自增列导致主键重复
文章转载:http://cdkjz.cn/article/ggecdg.html
多年建站经验

多一份参考,总有益处

联系快上网,免费获得专属《策划方案》及报价

咨询相关问题或预约面谈,可以通过以下方式与我们联系

大客户专线   成都:13518219792   座机:028-86922220