文章目录
- 一、replace into原理
- 二、`replace into`的三种形式
- 三、replace into 使用案例
- 四、replace into与on duplicate key update的区别
- 五、replace into的坑
官网地址:https://dev.mysql.com/doc/refman/8.0/en/replace.html
以下内容基于mysql 8.0版本进行讲解。
replace into与on duplicate key update在一定程度上都能实现无记录时插入,有记录时更新这个需求。但是强烈推荐使用on duplicate key update 原因见下方两者的区别。replace into的坑太多,若要使用请详细阅读本文。
一、replace into原理
replace into 跟 insert 功能类似,不同点在于:replace into 首先尝试插入数据到表中, 1. 如果发现表中已经有此行数据(根据主键或者唯一索引判断)则先删除此行数据,然后插入新的数据。 2. 否则,直接插入新数据。
要注意的是:插入数据的表必须有主键或者是唯一索引!否则的话,replace into与insert into功能一致,会直接插入数据,这将导致表中出现重复的数据。
如果归纳一下 REPLACE INTO 语句的执行规则,基本会有以下几种情况:
- 当表没有主键和唯一键时,REPLACE INTO 相当于普通的 INSERT 操作;binlog 记录事件为 INSERT;返回影响行数为 INSERT 的数量。
- 当表有主键没有唯一键时,REPLACE INTO 插入记录与主键冲突时会进行 DELETE+INSERT 操作;binlog 记录事件为 UPDATE;返回影响行数为 DELETE+INSERT 的数量;如果主键自增,则 AUTO_INCREMENT 值不变。
- 当表有唯一键没有主键时,REPLACE INTO 插入记录与唯一键冲突时会进行 DELETE+INSERT 操作;binlog 记录事件为 UPDATE;返回影响行数为 DELETE+INSERT 的数量。
- 当表同时存在主键和唯一键时,REPLACE INTO 插入记录与主键冲突的时候进行 DELETE+INSERT 操作;binlog 记录事件为 DELETE+INSERT;返回影响行数为 DELETE+INSERT 的数量。如果主键自增,则 AUTO_INCREMENT 值不变。
- 当表同时存在主键和唯一键时,REPLACE INTO 插入记录与唯一键冲突的时候进行 DELETE+INSERT 操作;binlog 记录事件为 UPDATE;返回影响行数为 DELETE+INSERT 的数量。如果主键自增,则新插入的这条记录的主键会变成最新 AUTO_INCREMENT 的值,而对应的