Mysql中REPLACE INTO详解及和INSERT INTO的区别

news/2024/11/19 3:33:05/

前言

我们在进行数据库操作的时候,经常会遇到这样的场景:

  1. 首先判断数据是否存在;
  2. 如果不存在,则插入;
  3. 如果存在,则更新。

博主之前是是Java来进行逻辑判断,例如:

看起来似乎也很简洁,但是博主在一次偶然的机会接触到了REPLACE INTO。至此,这些 Java 代码全部可以省略!

接下来,我将带领大家一起学习REPLACE INTO这个强大的 SQL 语句。


官方地址:

MySQL :: MySQL 8.0 Reference Manual :: 15.2.12 REPLACE Statement

1. REPLACE INTO 原理

REPLACE INTOINSERT INTO功能类似,不同点在于:

  • 若表中的一条旧记录与一条使用PRIMARY KEY主键索引或使用UNIQUE 唯一索引的新记录具有相同的值,则先删除旧数据,再插入新数据。
  • 否则,直接插入新数据。`

尤其需要注意的是:

除非表有一个 PRIMARY KEYUNIQUE 索引,否则,使用一个 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的数量。

总结

下面,我用表格来总结下使用 REPLACE INTO时可能会遇到的不同情况

  • 无主键无唯一索引

场景

REPLACE INTO 是相当于

REPLACE INTO 在 binlog 中的表现形式

返回的影响行数

无主键无唯一索引

INSERT

INSERT

INSERT 行数

  • 只有主键

场景

REPLACE INTO 是相当于

REPLACE INTO 在 binlog 中的表现形式

返回的影响行数

主键冲突

DELETE+INSERT

UPDTATE

DELETE+INSERT 行数

主键不冲突

INSERT

INSERT

INSERT 行数

  • 只有唯一索引

场景

REPLACE INTO 是相当于

REPLACE INTO 在 binlog 中的表现形式

返回的影响行数

唯一索引冲突

DELETE+INSERT

UPDTATE

DELETE+INSERT 行数

  • 有主键有唯一索引

场景

REPLACE INTO 是相当于

REPLACE INTO 在 binlog 中的表现形式

返回的影响行数

主键冲突唯一索引不冲突

DELETE+INSERT

DELETE+INSERT

DELETE+INSERT 行数

主键不冲突唯一索引冲突

DELETE+INSERT

UPDATE

DELETE+INSERT 行数

主键冲突唯一索引冲突

DELETE+INSERT

DELETE+INSERT

DELETE+INSERT 行数

主键不冲突唯一索引不冲突

INSERT

INSERT

INSERT 行数


2. REPLACE INTO 三种使用方式

value1,value2 均为模拟的表的属性名

REPLACE INTO 表名(value1, value2, ...) values(...);
REPLACE INTO 表名(value1, value2, ...) SELECT ....
REPLACE INTO 表名 SET value1 = value, ...

第一种形式与INSERT INTO的用法类似。

第二种形式相似于 INSERT SELECT。这种用法并不强制要求列名匹配,事实上,MYSQL 甚至不关心SELECT返回的列名,它需要的是列的位置。

eg:REPLACE INTO tb1( name, title, mood) SELECT rname, rtitle, rmood FROM tb2; 这个例子使用REPLACE INTO从 tb2 中将全部数据导入 tb1 中。

第三种形式相似于UPDATE SET用法。


3. REPALCE INTO 的坑

3.1. REPLACE INTO 与数据库设置自动更新时间的坑

REPLACE INTO在有主键或唯一索引冲突时,会执行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');

此时执行 REPLACE INTO

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

上图可以看出执行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());

3.2. REPLACE INTO 匹配唯一索引更新时会导致主键自增值+1

当有主键有唯一索引且唯一索引冲突时并且主键设置成 AUTO_INCREMENT,那么使用REPLACE INTO会导致 AUTO_INCREMENT值自增 1。

3.3. REPLACE INTO 同事有主键和唯一索引冲突时可能会删除多条数据

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

4. 总结

REPLACE INTO存在暗坑。需谨慎使用。如果要实现上述说的需求可以使用另一种方式:on duplicate key update

当然,on duplicate key update为 Mysql 特有语法并且最好在单线程下操作。不然多线程下INSERT经常会导致锁表的情况发生,也是个小坑。在使用这些技术的时候,我们需要多加注意


http://www.ppmy.cn/news/1548139.html

相关文章

QDataStream

本文来自: 智谱清言 Qt助手 ------ QDataStream 是 Qt 框架中的一个类,用于序列化和反序列化二进制数据。它允许你将基本数据类型(如 int、double、QString 等)以及 Qt 容器类(如 QList、QMap 等)写入到…

动态IP代理技术详解与实现

目录 一、动态IP代理技术概述 二、动态IP代理技术的原理 代理服务器中转: IP地址动态更换: 协议支持: 智能调度机制: 三、动态IP代理的实现方法 基于HTTP代理的实现: 正向代理: 反向代理&#xf…

用 Python 从零开始创建神经网络(八):梯度、偏导数和链式法则

梯度、偏导数和链式法则 引言1. 偏导数2. 和的偏导数3. 乘法的偏导数4. Max 的偏导数5. 梯度(The Gradient)6. 链式法则(The Chain Rule) 引言 在我们继续编写我们的神经网络代码之前,最后两个需要解决的难题是梯度和…

大语言模型通用能力排行榜(2024年11月8日更新)

数据来源SuperCLUE 榜单数据为通用能力排行榜 排名 模型名称 机构 总分 理科 文科 Hard 使用方式 发布日期 - o1-preview OpenAI 75.85 86.07 76.6 64.89 API 2024年11月8日 - Claude 3.5 Sonnet(20241022) Anthropic 70.88 82.4…

树莓派(Raspberry Pi)picotool

树莓派(Raspberry Pi)picotool 安装直接安装从源码安装工具介绍显示信息保存程序二进制信息基本信息引脚完整信息 链接 安装 直接安装 在archlinux上,使用yay直接安装 yay -S picotool从源码安装 安装libusb sudo pacman -S libusb下载 …

号卡分销系统,号卡系统,物联网卡系统源码安装教程

号卡分销系统,号卡系统,物联网卡系统,,实现的高性能(PHP协程、PHP微服务)、高灵活性、前后端分离(后台),PHP 持久化框架,助力管理系统敏捷开发,长期持续更新中。 主要特性 基于Auth验证的权限…

「人眼视觉不再是视频消费的唯一形式」丨智能编解码和 AI 视频生成专场回顾@RTE2024

你是否想过,未来你看到的电影预告片、广告,甚至新闻报道,都可能完全由 AI 生成? 在人工智能迅猛发展的今天,视频技术正经历着一场前所未有的变革。从智能编解码到虚拟数字人,再到 AI 驱动的视频生成&#…

Unet++改进24:添加DualConv||轻量级深度神经网络的双卷积核

本文内容:添加DualConv 目录 论文简介 1.步骤一 2.步骤二 3.步骤三 4.步骤四 论文简介 卷积神经网络(CNN)架构通常对内存和计算要求很高,这使得它们在硬件资源有限的嵌入式系统中不可行。 我们提出了双卷积核(DualConv)来构建轻量级深度神经网络。DualConv结合3 3和1…