一、replace into语法

官网信息.png

官网地址：https://dev.mysql.com/doc/refman/5.6/en/replace.html

可以看到，replace into的语法描述就是：如果新插入行的主键或唯一键在表中已经存在，则会删除原有记录并插入新行;如果在表中不存在，则直接插入。

二、binlog同步的replace into语句

搭建环境：
mac下mysql如何开启binlog
JAVA实现mysql的binlog监听（mysql-binlog-connector）

2.1 表中不存在

测试sql：

replace into t_user(id,age,name) Values(1,3,'tt');

binlog监听到的数据：

监听的事件类型:QUERY
监听的事件类型:TABLE_MAP
监听的事件类型:EXT_WRITE_ROWS
11:46:17.379 [main] INFO com.tellme.test.TestReplace - {"includedColumns":{"empty":false},"rows":[[1,"tt",3]],"tableId":70}
监听的事件类型:XID

结论：可以看到就是一条insert语句操作；

2.2 表中存在

测试sql

replace into t_user(id,age,name) Values(1,4,'tt');

binlog监听到的数据：

监听的事件类型:QUERY
监听的事件类型:TABLE_MAP
监听的事件类型:EXT_UPDATE_ROWS
11:48:44.897 [main] INFO com.tellme.test.TestReplace - {"includedColumns":{"empty":false},"includedColumnsBeforeUpdate":{"empty":false},"rows":[{"key":[1,"tt",3],"value":[1,"tt",4]}],"tableId":70}
监听的事件类型:XID

结论：可以看到就是一条update语句操作；

2.3 结论

当表中存在唯一键时，replace语句在主库就是删除+插入操作，但是在从库就只是update操作（binlog下发）。

三、replace into的风险

由2.3的结论可知道，主库和从库执行的sql并不是相同的，可能会带来一些风险（主从一致性即主库的AUTO_INCREMENT可能会比从库的AUTO_INCREMENT要高，当主从切换时，可能会出现重复key的异常）

3.1 场景1：只命中唯一索引

CREATE TABLE `t_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '名字',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

使用场景：更新场景下，我们查询到旧数据，然后将需要更换的值填充到旧对象中，然后将其旧对象使用replace into的语法更新到数据库中。假设此时只是命中了主键id（唯一索引）。
场景：

replace into t_user(id,age,name) Values(1,4,'tt');

此时：主库和从库的AUTO_INCREMENT会保持一致，不会出现上述现象。

3.2 场景2：只命中普通字段的唯一索引

表结构：

CREATE TABLE `t_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '名字',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`),
  UNIQUE KEY `union_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

执行sql（关键点，没有将id传入）

replace into t_user(age,name) Values(5,'tt4');

binlog监听：

监听的事件类型:QUERY
监听的事件类型:TABLE_MAP
监听的事件类型:EXT_UPDATE_ROWS
13:05:46.616 [main] INFO com.tellme.test.TestReplace - {"includedColumns":{"empty":false},"includedColumnsBeforeUpdate":{"empty":false},"rows":[{"key":[6,"tt4",4],"value":[7,"tt4",5]}],"tableId":74}

结论：此时主库插入了一条id=7的记录，故主库的AUTO_INCREMENT会变为8，同步给从库的语句只是一条update语句，从库的AUTO_INCREMENT依旧为7。在主从切换时，便会出现上述的问题。

3.3 命中主键和普通字段唯一索引

表结构见3.2：

表数据：

表中数据.png

诉求：表中查询到旧记录（3,'tt3',1），前端传入的是将tt3修改了tt2。经过转化语句变成了：

replace into t_user(id,name,age) Values(3,tt2',1);

由此可见：影响的是3条记录（而非之前认知2条数据）

image.png

sql执行后的结果：

image.png

原理：因为命中了唯一索引id和唯一索引name两个字段。

主库：会先执行delete name='tt2'和delete id='3'的操作，然后在执行insert name='tt2' and id='3'故产生了3条语句。

从库：先删除了id=3的记录，然后将（2,'tt2',13）更换成了（3,"tt2",1）。

监听的事件类型:QUERY
监听的事件类型:TABLE_MAP
监听的事件类型:EXT_DELETE_ROWS
13:21:08.322 [main] INFO com.tellme.test.TestReplace - {"includedColumns":{"empty":false},"rows":[[3,"tt3",1]],"tableId":74}
监听的事件类型:EXT_UPDATE_ROWS
13:21:08.322 [main] INFO com.tellme.test.TestReplace - {"includedColumns":{"empty":false},"includedColumnsBeforeUpdate":{"empty":false},"rows":[{"key":[2,"tt2",13],"value":[3,"tt2",1]}],"tableId":74}
监听的事件类型:XID

结论：可能会产生一些非预期内的问题。即在实现的时候没有料想到会命中其他字段的唯一索引。

四、结论

replace语句虽然可以比较快捷的实现我们的需求，但是并不太推荐使用。

风险点：

1. 可能造成主从不一致的情况；
2. 存在多个唯一索引时，可能会产生预期外的sql执行；