慎用mysql replace语句

• 如果业务逻辑强依赖自增ID，建议不要用REPLACE

• 当存在PK冲突的时候是先DELETE再INSERT

• 当存在UK冲突的时候是直接UPDATE，UPDATE操作不会涉及到AUTO_INCREMENT的修改

• 很大程度上会导致主备中断，存在容灾风险

REPLACE的语法

原理

REPLACE的工作机制有点像INSERT,只不过如果在表里如果一行有PRIMARY KEY或者UNIQUE索引，那么就会把老行删除然后插入新行。如：

这样的风险点:

尽管主备库数据是一致的，但是主备库切换后，备库因AUTO_INCREMENT小于实际数据的最大值，这样会导致写入失败，失败一次后，会更新AUTO_INCREMENT为最大值+1；所以，一些REPLACE操作建议使用INSERT INTO tbname ... VALUES ... ON DUPLICATE KEY UPDATE col1=。

建议：

如果业务逻辑强依赖自增ID，绝对不要用REPLACE，普通环境也不建议这样用，因为会导致主键的重新组织

疑问：

既然UK冲突的时候是UPDATE，那么为什么affect_rows都是2呢？让我们从源码上分析看下：

用的时候需要注意的是：

如果指定REPLACE列的话，尽量写全，要不然没有输入值的列数据会被赋成默认值（因为是先DELETE在INSERT），就和普通的INSERT是一样的，所以如果你要执行REPLACE语句的话是需要INSERT和DELETE权限的。

如果你需要执行 SET col_name = col_name + 1,就相当于执行col_name = DEFAULT(col_name) + 1.

REPLACE语句如果不深入看的话，就和INSERT一样，执行完后没什么反应。例：

MySQL给REPLACE和LOAD DATA....REPLACE用的算法是:

1. 尝试向表里插入新行

2. 当表里唯一索引或者PRIMARY KEY冲突的时候：

• DELETE冲突行

• 往表里再次插入新行

如果遇到重复行冲突，存储过程很可能当作UPDATE执行，而不是DELETE+INSERT，但是显式上都是一样的。这里没有用户可见的影响除了存储引擎层Handler_xxx的状态变量。

因为REPLACE ... SELECT语句的结果依赖于SELECT的行的顺序，但是顺序没办法保证都是一样的，有可能从MASTER和SLAVE的都不一样。正是基于这个原因，MySQL 5.6.4以后，REPLACE ... SELECT语句被标记为基于STATEMENT的复制模式不安全的。基于这个变化，当使用STATEMENT记录二进制日志的时候，如果有这样的语句就会在log里面输出一个告警,同样当使用MIXED行复制模式也会记录告警。

在MySQL5.6.6之前的版本，REPLACE影响分区表就像MyISAM使用表级锁锁住所有的分区表一样。当使用 REPLACE ... PARTITION语句时确实会发生上述情况。(使用基于行锁的InnoDB引起不会发生这种情况。)在MySQL 5.6.6以后的版本MySQL使用分区锁，只有当分区(只要没有分区表的列更新)包含了REPLACE语句并且WHERE实际匹配到的才会锁住那个分区;否则的话就会锁住整个表。

操作形式：

作者：阿里云云栖社区
链接：https://www.jianshu.com/p/a055b4778769