问题:数据库迁移后,执行语句 load data local infile '%s' replace into table %s (%s)。
会出现 Duplicate entry 的问题
原因:replace 语句会被拆分成 delete + insert ,在执行insert 语句的时候,AUTO_INCREMENT 会执行+1 操作。 --》 主库AUTO_INCREMENT会+1.
但是在binlog只产生一条update语句。 --》从库的AUTO_INCREMENT不会 +1。
详细了解下~
一。准备知识:insert会使AUTO_INCREMENT +1 ,而update 不会。
先创建一个表 AUTO_INCREMENT 为1
插入一条数据
insert into person(name,age) values("vic","23");
发现AUTO_INCREMENT 变为2
执行update
update person set name = 'mu' where name = 'vic'
发现AUTO_INCREMENT 还是2
so~ insert使AUTO_INCREMENT +1 (insert 失败AUTO_INCREMENT也会 +1),而update并没有。
二 replace 的操作。主、从执行不一致
replace操作对于已经存在的数据进行update操作,而update实际是delete+insert两个连续操作 --》 主库
binlog产生一条update语句,这里binlog_format=ROW --》从库
三 迁库前的机器状态
在执行 load data local infile '%s' replace into table %s (%s) 后。
主库状态(A) | 从库状态(B) |
id = 1 | id = 1 |
AUTO_INCREMENT = 2 | AUTO_INCREMENT = 1 |
由于 AUTO_INCREMENT=1,所以插入数据时id设为1。但是数据库中已经存在一条id为1的记录,会报主键冲突。
但是,在次执行插入操作,不会出现主键冲突 ,因为执行insert 操作是使得 AUTO_INCREMENT+1,而实际上mysql是通过select max(id)+1 from t的方法来拿到auto_inrement的值。 所以此时AUTO_INCREMENT = 2.
五 最后
很棒的微博: https://mckobe23.github.io/2016/07/22/replace-autoincrement/