MySQL中replace into详解、批量更新、不存在插入存在则更新、replace into的坑

冷不防 2024-04-06 15:44 21阅读 0赞

#### 文章目录 ####

*  一、replace into原理
 *  二、\`replace into\`的三种形式
 *  三、replace into 使用案例
 *   *  3.1、replace into values
     *   *  3.1.1、只有主键且主键冲突
         *  3.1.2、有主键有唯一索引且主键冲突
         *  3.1.3、有主键有唯一索引且唯一索引冲突(有坑)
         *  3.1.4、有主键有唯一索引且与一条主键冲突与另一条唯一键冲突(有坑)
     *  3.2、replace into select
     *  3.3、replace into set
     *   *  3.3.1、唯一索引不重复
         *  3.3.2、唯一索引重复
 *  四、replace into与on duplicate key update的区别
 *  五、replace into的坑
 *   *  5.1、replace into与自动更新时间的坑
     *  5.2、replace into用唯一索引更新时会导致自增值+1
     *  5.3、replace into同时有主键或唯一索引冲突时可能会删除多条记录

官网地址：[https://dev.mysql.com/doc/refman/8.0/en/replace.html][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 语句的执行规则，基本会有以下几种情况：

1.  当表没有主键和唯一键时，REPLACE INTO 相当于普通的 INSERT 操作；binlog 记录事件为 INSERT；返回影响行数为 INSERT 的数量。
2.  当表有主键没有唯一键时，REPLACE INTO 插入记录与主键冲突时会进行 DELETE+INSERT 操作；binlog 记录事件为 UPDATE；返回影响行数为 DELETE+INSERT 的数量；如果主键自增，则 AUTO\_INCREMENT 值不变。
3.  当表有唯一键没有主键时，REPLACE INTO 插入记录与唯一键冲突时会进行 DELETE+INSERT 操作；binlog 记录事件为 UPDATE；返回影响行数为 DELETE+INSERT 的数量。
4.  当表同时存在主键和唯一键时，REPLACE INTO 插入记录与主键冲突的时候进行 DELETE+INSERT 操作；binlog 记录事件为 DELETE+INSERT；返回影响行数为 DELETE+INSERT 的数量。如果主键自增，则 AUTO\_INCREMENT 值不变。
5.  当表同时存在主键和唯一键时，REPLACE INTO 插入记录与唯一键冲突的时候进行 DELETE+INSERT 操作；binlog 记录事件为 UPDATE；返回影响行数为 DELETE+INSERT 的数量。如果主键自增，则新插入的这条记录的主键会变成最新 AUTO\_INCREMENT 的值，而对应的 `AUTO_INCREMENT 值会 +1`。
6.  当表同时存在主键和唯一键时，REPLACE INTO 插入记录与一条记录主键和一条记录唯一键都冲突的时候进行 DELETE+INSERT 操作；binlog 记录事件为 DELETE+UPDATE；返回影响行数为 DELETE\*2+INSERT 的数量。

第五点会有副作用：见本文《3.1.3、有主键有唯一索引且唯一索引冲突(有坑)》  
第六点会有副作用：见本文《3.1.4、有主键有唯一索引且与一条主键冲突与另一条唯一键冲突(有坑)》

**总结**

> 以下总结replace into时可能会遇到的场景  
> pk：主键 uk：唯一索引

*  **`无pk无uk`**

<table> 
 <thead> 
  <tr> 
   <th>场景</th> 
   <th>replace into时相当于</th> 
   <th>replace into在binlog中的表现形式</th> 
   <th>返回影响行数</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>无pk无uk</td> 
   <td>insert</td> 
   <td>insert</td> 
   <td>insert行数</td> 
  </tr> 
 </tbody> 
</table>

*  **`只有pk`**

<table> 
 <thead> 
  <tr> 
   <th>场景</th> 
   <th>replace into时相当于</th> 
   <th>replace into在binlog中的表现形式</th> 
   <th>返回影响行数</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>pk冲突</td> 
   <td>delete+insert</td> 
   <td>update</td> 
   <td>delete+insert行数</td> 
  </tr> 
  <tr> 
   <td>pk不冲突</td> 
   <td>insert</td> 
   <td>insert</td> 
   <td>insert行数</td> 
  </tr> 
 </tbody> 
</table>

*  **`只有uk`**

<table> 
 <thead> 
  <tr> 
   <th>场景</th> 
   <th>replace into时相当于</th> 
   <th>replace into在binlog中的表现形式</th> 
   <th>返回影响行数</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>uk冲突</td> 
   <td>delete+insert</td> 
   <td>update</td> 
   <td>delete+insert行数</td> 
  </tr> 
 </tbody> 
</table>

*  **`有pk有uk`**

<table> 
 <thead> 
  <tr> 
   <th>场景</th> 
   <th>replace into时相当于</th> 
   <th>replace into在binlog中的表现形式</th> 
   <th>返回影响行数</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>pk冲突 uk不冲突</td> 
   <td>delete + insert</td> 
   <td>delete + insert</td> 
   <td>delete+insert行数</td> 
  </tr> 
  <tr> 
   <td>pk不冲突 uk冲突</td> 
   <td>delete + insert</td> 
   <td>update</td> 
   <td>delete+insert行数</td> 
  </tr> 
  <tr> 
   <td>pk冲突 uk冲突</td> 
   <td>delete + insert</td> 
   <td>delete+insert</td> 
   <td>delete+insert行数</td> 
  </tr> 
  <tr> 
   <td>pk不冲突 uk不冲突</td> 
   <td>insert</td> 
   <td>insert</td> 
   <td>insert行数</td> 
  </tr> 
 </tbody> 
</table>

## 二、`replace into`的三种形式 ##

1. replace into tbl_name(col_name, ...) values(...)
    
    2. replace into tbl_name(col_name, ...) select ...
    
    3. replace into tbl_name set col_name=value, ...

第一种形式相似于insert into的用法，

第二种replace select的用法也相似于insert select，这种用法并不必定要求列名匹配，事实上，MYSQL甚至不关心select返回的列名，它需要的是列的位置。例如，replace into tb1( name, title, mood) select rname, rtitle, rmood from tb2; 这个例子使用replace into从 tb2中将全部数据导入tb1中。

第三种replace set用法相似于update set用法，使用一个例如“SET col\_name = col\_name + 1”的赋值，则对位于右侧的列名称的引用会被做为DEFAULT(col\_name)处理。所以，该赋值至关于SET col\_name = DEFAULT(col\_name) + 1。

前两种形式用的多些。其中 “into” 关键字能够省略，不过最好加上 “into”，这样意思更加直观。另外，对于那些没有给予值的列，MySQL 将自动为这些列赋上默认值。

## 三、replace into 使用案例 ##

### 3.1、replace into values ###

#### 3.1.1、只有主键且主键冲突 ####

> 当只有主键且主键冲突时，会执行delete+insert操作

如下sql:

drop table if exists user_test;
    create table user_test(
    	id int primary key auto_increment comment '主键',
    	name varchar(30)  not null comment '姓名',
    	update_time timestamp on update CURRENT_TIMESTAMP comment '更新时间'
    ) comment '测试表';
    
    insert into user_test(name,update_time) value ('zhangsan','2000-01-01 00:00:00');

![在这里插入图片描述][b4132a45db8343d68f45055919fe13cd.png]  
此时执行replace into

replace into user_test(id,name) value (1,'zhangsan');

![在这里插入图片描述][0591c6bc73b14503aa2d96257ed9cd73.png]  
上图可以看出执行replace into之后，返回的受影响条数是2，且update\_time的字段被更新为空了。由此证明replace into时当只有主键且主键冲突时执行了delete+insert操作。

注意： 当时间字段设置自动更新后，若没有设置默认值，replace into更新时需要手动赋予时间字段，否则会插入为空。

#### 3.1.2、有主键有唯一索引且主键冲突 ####

> 当有主键有唯一索引且主键冲突时，会执行delete+insert操作

drop table if exists user_test;
    create table user_test(
    	id int primary key auto_increment comment '主键',
    	name varchar(30) unique not null comment '姓名',
    	update_time timestamp on update current_timestamp comment '更新时间'
    ) comment '测试表';
    
    insert into user_test(name,update_time) value ('zhangsan','2000-01-01 00:00:00');
    
    replace into user_test(id,name) value (1,'lisi');

![在这里插入图片描述][585257971cd64893b905b049fbd94525.png]  
上图可以看出执行replace into之后，返回的受影响条数是2，且update\_time的字段被更新为空了。由此证明replace into时当有主键有唯一索引且主键冲突时执行了delete+insert操作。

#### 3.1.3、有主键有唯一索引且唯一索引冲突(有坑) ####

> 当有主键有唯一索引且唯一索引冲突时，会执行delete+insert操作，且AUTO\_INCREMENT自增1

![在这里插入图片描述][58d0f1a8f0b24d4aac2bfcae8c0c9637.png]  
此时执行replace into语句

replace into user_test(name) value ('zhangsan');

![在这里插入图片描述][b61e961120e744cb96a4b6df696d5cc8.png]  
上图可以看出执行replace into之后，返回的受影响条数是2，且update\_time的字段被更新为空了。由此证明replace into时当有主键有唯一索引且唯一索引冲突时执行了delete+insert操作。

注意： `唯一索引冲突时会带来副作用，主键id被改变了。`由于主键设置了自增，所以当唯一索引冲突时导致AUTO\_INCREMENT的值自增1。

#### 3.1.4、有主键有唯一索引且与一条主键冲突与另一条唯一键冲突(有坑) ####

> 当有主键与唯一索引时，若replace into与其中一条主键冲突、与另一条记录唯一索引冲突，此时执行delete+insert操作。注意：此时受影响的条数是3，且会删除两条数据，可能会有坑

drop table if exists user_test;
    create table user_test(
    	id int primary key auto_increment comment '主键',
    	name varchar(30) unique not null comment '姓名',
    	update_time timestamp on update current_timestamp comment '更新时间'
    ) comment '测试表';
    
    insert into user_test(id,name,update_time) values
    (1,'zhangsan','2000-01-01 00:00:00'),
    (2,'lisi','2000-01-01 00:00:00');
    
    replace into user_test(id,name) value (1,'lisi');

![在这里插入图片描述][6aed9f8e448049d4a74def922a0db525.png]  
上图可以看出执行replace into之后，返回的受影响条数是3。

注意： 在该场景下会删除2条数据，然后插入1条数据，所以可能会造成坑，需要特别注意。

### 3.2、replace into select ###

语法类似insert into select

示例：
    replace into t(id, update_time) select 1, now();
    replace into tb1( name, title, mood) select rname, rtitle, rmood from tb2;

### 3.3、replace into set ###

表结构如下：

drop table if exists user_test;
    create table user_test(
    	id int primary key auto_increment comment '主键',
    	name varchar(30) unique not null comment '姓名',
    	age int default 2 comment '年龄',
    	sex varchar(10) comment '性别：男、女'
    ) comment '测试表';

![在这里插入图片描述][1e210cd8cb0b48ea85aaefcfc3244e4a.png]

其中： name为唯一索引，age默认值是2

#### 3.3.1、唯一索引不重复 ####

执行以下sql插入第一条数据

replace into user_test set name = 'zhangsan',age = age+1, sex = '男';

![在这里插入图片描述][c6cdae1aef334c96aa1f9cc7f9dfa7cf.png]

可以看到age=3是因为age=default(age)+1，默认age=2然后又+1所以是3；若age字段建表时没有设置默认值则上述sql执行完后age为null。

#### 3.3.2、唯一索引重复 ####

接着上面的示例，运行以下sql

replace into user_test set name = 'zhangsan',age = age+1;

![在这里插入图片描述][053169a347b2498d8b35d087d48a760d.png]

我们看到id=1的数据被删除了，新增了一条id=2的数据；它的sex字段的值没有继承第一条数据的值，age属性的值也只是由默认值决定，与上一条的值无关。

**由此证明结论：**

1.  在没有唯一键/主键重复时，replace into所做的事情就是新添加一个条目，条目各个属性的值与运行的语句内容有关。
2.  在有唯一键/主键重复时，replace into所做的事情就是

*  直接删除掉那条重复的旧条目
 *  然后新添加一个条目。

**新条目各个属性的值只取决于运行的语句内容，与被删除的旧条目的值无关。**

## 四、replace into与on duplicate key update的区别 ##

> on duplicate key update更多内容参见《[MySQL中ON DUPLICATE KEY UPDATE的介绍与使用、批量更新、存在即更新不存在则插入][MySQL_ON DUPLICATE KEY UPDATE] – 五月天的尾巴》

> replace into与on duplicate key update在一定程度上都能实现无记录时插入，有记录时更新这个需求。但是强烈推荐使用on duplicate key update 原因如下：
> 
> 1.  replace into在唯一索引冲突时，会删除原记录，然后新增一条记录，如果主键id是自增的会导致主键被改变； 而on duplicate key update在唯一索引冲突时是更新原记录，主键不变。
> 2.  replace into唯一索引冲突时会导致主键自增值增加，但由于 binlog 事件记录为 UPDATE 会导致主从环境中表的 AUTO-INCREMENT 值不同，从库执行 UPDATE 事件并不会导致 AUTO-INCREMENT 值增加，所以从库表的 AUTO-INCREMENT 值会小于等于当前表的最新记录主键，当发生主从切换时向新的主库插入记录就会报 duplicate key 错误。
> 
> 鉴于此，很多使用 REPLACE INTO 的场景，实际上需要的是 INSERT INTO … ON DUPLICATE KEY UPDATE（虽然也会增加自增值，但是不会出现从库表的 AUTO-INCREMENT 值会比当前表的最新记录主键小的情况），与 ‘REPLACE INTO …’ 不同，它只是更新重复行上的值，没有删除，也就不会导致原有主键值的变化。

1：replace into  
2：on duplicate key update

一，两者在数据库中无记录时都执行插入操作；在数据库中有记录时都执行“更新”操作。  
二，二者在判断有无记录时，都是根据表里的主键或唯一索引。  
三，检索到存在记录时，1是先删除原记录，再插入新记录；而2是保持原记录不变，更新指定的字段。

如表中有如下记录，其中id是自增主键，col1是唯一索引：  
(id, col1, col2,col3)= (1, a,b,c)

执行  
replace into tabelname(col1, col2) values (a,d)  
则表中数据变为  
(id, col1, col2,col3)= (2, a,d,null)  
原记录完全删除，id发生自增。

执行  
insert into tablename(col1) values (a)  
on duplicate key update col2=d  
则表中数据变位  
(id, col1, col2,col3)= (1, a,d,c)  
col3保持不变，id未自增，不过在插入下一条记录时，id会跳跃。

## 五、replace into的坑 ##

### 5.1、replace into与自动更新时间的坑 ###

replace into在有主键或唯一索引冲突时，会执行delete+insert操作，若字段设置了自动更新，需要手动设置时间或者字段设置默认时间，否则时间字段会插入为空

如下sql:

![在这里插入图片描述][b4132a45db8343d68f45055919fe13cd.png]  
此时执行replace into

replace into user_test(id,name) value (1,'zhangsan');

![在这里插入图片描述][0591c6bc73b14503aa2d96257ed9cd73.png]  
上图可以看出执行replace into之后，update\_time的字段被更新为空了。

**解决方案**

# 方案一：建表时设置字段有默认时间
    create table user_test(
    	id int primary key auto_increment comment '主键',
    	name varchar(30)  not null comment '姓名',
    	update_time timestamp default current_timestamp on update CURRENT_TIMESTAMP comment '更新时间'
    ) comment '测试表';
    
    # 方案二：replace into时手动设置时间
    replace into user_test values(1,'zhangsan',now());

### 5.2、replace into用唯一索引更新时会导致自增值+1 ###

见本文《3.1.3、有主键有唯一索引且唯一索引冲突(有坑)》

### 5.3、replace into同时有主键或唯一索引冲突时可能会删除多条记录 ###

注意：使用replace into时只能用主键更新或者唯一键更新，二选其一。若同时都冲突了，可能会导致异常

见本文《3.1.4、有主键有唯一索引且与一条主键冲突与另一条唯一键冲突(有坑)》

[https_dev.mysql.com_doc_refman_8.0_en_replace.html]: https://dev.mysql.com/doc/refman/8.0/en/replace.html
[b4132a45db8343d68f45055919fe13cd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/19803c451b2c4065983d8e9392b49971.png
[0591c6bc73b14503aa2d96257ed9cd73.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/dc5a6a3d70a446f6a86eaea718bd9cb6.png
[585257971cd64893b905b049fbd94525.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/e8d11edeb2d6444ab1ff3c64cce28d07.png
[58d0f1a8f0b24d4aac2bfcae8c0c9637.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/7597829529804f50b019327e83202e18.png
[b61e961120e744cb96a4b6df696d5cc8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/958e11bc7cdc446a88a33064df3d8d00.png
[6aed9f8e448049d4a74def922a0db525.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/95880b4e28444842b8bcc6615d2740b9.png
[1e210cd8cb0b48ea85aaefcfc3244e4a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/4a5f5bd88d2d4737a1fdbc6f45a55349.png
[c6cdae1aef334c96aa1f9cc7f9dfa7cf.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/317afb2104aa42fb8934c157a3c5a4a7.png
[053169a347b2498d8b35d087d48a760d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/850a0a798d4040659baf4c7c8c26466b.png
[MySQL_ON DUPLICATE KEY UPDATE]: https://blog.csdn.net/weixin_49114503/article/details/136479860