MySQL有哪些实现方式?何为插入,何为更新?

mysql特殊语法insert into .. on duplicate key update ..使用详解 阅读详情

需求背景

环境

MySQL 版本: 5.7.20-log

开发规范

公司后端开发规范有这么一点:

更新数据库表中数据的时候,不允许先删,然后批量插入

需要将参与表中数据比判断,找出哪些是新插入,哪些需要更新,哪些是删除的,然后再做对应的数据操作

需求

我们有 表 如下:

当商品配送完后之后,需要记录它的最新配送价,若商品最新配送价已经存在则进行更新,不存在则执行插入

针对这个需求,我们有哪些实现方式?

代码处理

按开发规范中说的处理

通过代码在内存中进行数据处理,找出插入列表与更新列表,然后执行数据库操作

因为是很常规的插入与更新操作,所以这种处理方式适用于所有的关系型数据库

REPLACE INTO

当数据库是 MySQL ,碰到 不存在则插入,存在则更新 的需求时,第一时间往往想到的是REPLACE INTO

工作原理

replace into 跟 insert 功能类似

不同点在于: replace into 首先尝试插入数据到表中,如果发现表中已经有此行数据(根据主键或者唯一索引判断)则先删除此行数据,然后插入新的数据,否则直接插入新数据

replace 语句会返回一个数,表示受影响的行的数目,该数是被删除和被插入的行数的和

我们来看个示例

对于示例结果,相信大家都能理解

需要强调的是:根据唯一索引 uk_comany_ware 判定 (1001,10001,19.8,1,1) 已经存在,那么先删除此记录,然后插入 (1001,10001,20.5,1,1)

而 (1001,10002,5.45,1,1) 判定为不存在,那么直接插入

这就导致我们看到的输出结果是: 受影响的行:3 ,同时自增主键由 1 变成了 2 3 ,而不是 1 2

有坑

正是因为 replace into 的工作原理,不可避免就产生了一些需要注意的地方

1、破坏外键约束

如果主键被指定成了其他表的外键,那么 replace into 更新(非插入)时影响到了其他表的外键约束,那么会执行失败,提示类似信息:

可能很多小伙伴会说:我们开发过程中,会遵循阿里开发手册中的规约,其中有一条规约如下:

我们不用外键了,也就不会出现前面的 [Err] 1451 错误了

其实阿里开发手册中的这条规约,不是说不让我们用外键,而是说不用数据库层面的外键约束,在应用代码层面解决外键逻辑

用数据库层面的外键,问题提示的很明显,也不会产生脏数据

而应用层解决外键,反而使外键约束的数据一致性问题更隐晦,产生脏数据,如下

从此我们踏上了修数据的不归路

2、主键加速自增

很多情况下,我们的主键是 int 或者 bigint 类型,并且设置成了自增

不管是 int 还是 bigint ,都有一个最大值,如果一直自增下去,总有一天会达到最大值(可能到地老天荒也达不到这个值)

replace into 的更新是先删除再插入,会导致主键自增 1(照理来说,更新是不应该导致主键自增 1)

如果更新频率远远大于插入频率,本不用考虑的自增主键用完的问题,可能就需要考虑了

另外也会导致主键不连续,主键值跳跃式的出现在表中

3、主从切换问题

master:master-local ,slave:slave-192.168.0.112 ,同步库:my_project

从上图可以看出,主从复制是正常的

接下来我们看看 replace into 对主从复制有什么影响

此时 master 与 slave 上的

t_ware_last_delivery_price 的下一个非手工指定的主键都是 11( AUTO_INCREMENT=11 ),两者是一致的

我们在 master 上使用 replace into 更新一条记录

master 与 slave 的数据是一致的,但是 master 上的下一个自增主键是 AUTO_INCREMENT=12 ,而 slave 上却是 AUTO_INCREMENT=11

可能会有人觉得:数据一致就行,下一个自增主键不一致有什么关系?

我们来想一下这个问题:如果 master 库崩了,我们会怎么做?会将 slave 提升为 master

此时问题就来了: slave 提升成 master 之前,实际数据的 id 已经到了 11 ,但其 AUTO_INCREMENT=11 ,也就说下一个自增主键是 11

那么下一条不指定 id 值的新纪录是插入时就会发生 duplicate key error ,每次冲突之后 AUTO_INCREMENT += 1,直到增长为 max(id) + 1 之后才能恢复正常

INSERT UPDATE

针对 不存在则插入,存在则更新 , MySQL 还提供了另外一种方言实现: INSERT ... ON DUPLICATE KEY UPDATE Statement

工作原理

如果指定 ON DUPLICATE KEY UPDATE 子句,并且要插入的行将导致唯一索引或主键中出现重复值,则会更新旧行,否则则是插入

例如,如果列 a 被声明为唯一且包含值 1,则以下两条语句具有类似的效果

但是这两条 SQL 的效果并不完全相同,我们以

t_ware_last_delivery_price 为例,来看看它们的区别

我们先来看看 UPDATE

只是对 id = 11 的 last_delivery_price 就行了修改,受影响的行只有 1,不会影响 AUTO_INCREMENT 的值

我们再来看看 INSERT INTO ... ON DUPLICATE KEY UPDATE

对 id = 11 的 last_delivery_price 进行了修改,受影响的行是 2,并且 AUTO_INCREMENT=13

此刻,我相信我们有共同的两个疑问

1、为什么受影响的行数是 2,而不是 1

2、自增主键为什么自增了 1( AUTO_INCREMENT 为什么等于 13,而不是原有的 12)

为什么受影响的行数是 2,而不是 1,官方文档有这么一段说明

意思就是:1 表示新插入一行,2 表示更新了一行,0 表示更新前后值未变

我们换个角度来理解,假设让我们来设计,一条 SQL 既能插入,也能更新,我们如何告知用户到底是插入成功了,还是更新成功了?

所以 1,2 仅仅只是用来区分插入和更新,2 并非真正受影响的行数

主键明明没有变化,为什么 AUTO_INCREMENT=13 自增了 1 ?

这和 MySQL 的主键自增的参数有关 innodb_autoinc_lock_mode ,它有 3 个值 0,1,2

mysql5.1 之后其默认值是 1

因为 innodb_autoinc_lock_mode = 1

所以上述 SQL 被当作简单插入处理,在真正修改数据之前就对 AUTO_INCREMENT 自增 1 处理了

批量操作

不仅支持单条操作,也支持批量操作

和批量插入类似

有坑

因为 innodb_autoinc_lock_mode = 1 是一个折中的选择,一般不会去改它,所以有些需要注意的点

1、主键加速自增

与 replace into 类似,即使是更新,也会导致 AUTO_INCREMENT 自增,加速了主键的衰老

同时也会导致主键的跳跃

2、主从切换问题

与 replace into 类似, master 上的更新导致 AUTO_INCREMENT 自增,而 AUTO_INCREMENT 又未同步到 slave

当 slave 升级成 master 后,可能会出现 duplicate key error

与 replace into 不同的是,上述两个问题可以通过设置 innodb_autoinc_lock_mode = 0 来避免,因为很多场景下对性能要求并不高。

总结

1、如何选择哪种方式

上述三种方式各有优略,代码处理不依赖于具体的数据库,可移植性高,也不会引入特定数据库的在这方面的缺陷

replace into 的方式不推荐(坑有点多),它完全可以由 INSERT UPDATE 替代

INSERT UPDATE 可以减少我们的代码,但它是 MySQL 的拓展实现,只有 MySQL 支持,可移植性差

2、针对 INSERT UPDATE 的 “坑”,我们可以结合具体的业务来设置 innodb_autoinc_lock_mode ,适当地避免它的 “坑”

3、道路千万条,合适第一条

针对某个需求,实现方式往往有很多,我们要做的就是从中找到最适合的那一条

Mysql数据库insert、replace、update、delete、truncate、drop、select语句介绍 阅读详情

相关推荐

MySQL数据库中实现记录的插入和更新操作有多种方式。在本文中,我们将讨论其中的几种常见实现方式,并提供相应的源代码示例。

如果你只需要插入一条新记录或更新现有记录的某些列,可以使用第一种方法。该方法首先尝试插入一条新记录,如果唯一键冲突,则从子查询中选择指定的列值进行更新。该语句首先尝试插入一条新记录,如果存在唯一键冲突,则删除旧记录并插入新记录。以上是几种常见的MySQL数据库中实现记录插入和更新的方法。替换为目标表的实际名称,并提供要插入或更新的列名和相应的值。替换为目标表的实际名称,并提供要插入或更新的列名和相应的值。替换为目标表的实际名称,并提供要插入或更新的列名和相应的值。替换为你要检查的唯一键的值。

HackGJN的博客 265

mysql的insert for update用法

好久没写博客了,洗完澡一时兴起,小写一篇记录sql知识点吧。 由于之前公司业务的变更,需要做一个历史数据表数据迁移到新建的数据表中,刚被分配到这个任务时,马上打开历史数据表的相关结构,一看懵逼了。需求大概是把原来的几百张数据库表(里面大概有三四类表,由于数据量太大,都进行一定规则的分表)迁移到新建的数据库表(也是采用一定规则分表)。 第一阶段,采用insert语法批量添...

奋斗吧,CQS! 1万+

步进电机励磁模式

1. 励磁模式 步进电机的基本操作模式称为 励磁模式,能够使步进电机工作在全步模式,半步模式和微步模式,其中微步模式能够有效的降低步进电机相电流的噪声,能够改善步进电机固有的噪声震动问题。下面将介绍 3 种励磁模式。 2. 全步励磁模式 所谓全步模式,就是依据电机固有结构设计固定的步距角工作,一个电脉冲,步进电机前进一个步距角。这个步距角使电机设计结构所决定的,也可以理解为电机以最大的步距角旋转。 3. 半步励磁模式 半步模式是以电机固有的结构决定的步距角的一半角度进行步进旋转。如下图所示,步进

zhbi98 的技术 Blogs 5551

MySQL数据库-DML常见命令 插入 insert into 更新update 删除delete

DML常见命令 insert into update delete 插入数据 insert into 方式一(不推荐,了解) insert into 表名 values(值1,值2,.....); ps:从命令行无法看到插入字段是什么,无法确定插入数据的个数,过分依赖与数据库的表结构顺序 在插入额时候,值顺序必须和表结构的顺序保持完全一致,且个数相同 方式二 (推荐) insert into 表名(字段1,字段2,.....) values(值1,值2,......); insert in

weixin_45006403的博客 765

如何用Hugo Stack主题打造极简技术博客?3个必改配置与5个高级美化技巧

本文详细解析了如何深度定制Hugo Stack主题,打造个性化技术博客。文章提供了3个必改配置与5个高级美化技巧,涵盖字体美学、布局重构、代码块视觉强化及细节打磨,帮助开发者超越基础部署,实现从功能到美学的进阶,提升博客质感与用户体验。

carrot的博客 570

优快云积分获取与管理全攻略

技术社区积分系统是知识共享生态的核心激励机制,通过量化用户贡献实现资源交换。其底层逻辑遵循Web2.0时代的UGC经济模型,用户通过内容创作、社区互动等行为积累虚拟资产。在开发者社区场景中,积分体系能有效促进技术文档、工具源码等资源的流通,优快云作为国内头部平台,其积分规则具有典型参考价值。掌握签到策略、内容互动技巧和资源上传规范等实操方法,可系统性提升积分获取效率。2023年新规强化了连续签到奖励和互动质量检测机制,结合GitHub等开源平台资源互补,能构建更高效的技术资源获取路径。

weixin_34233974的博客 218

记录不存在则插入,存在则更新 → MySQL 的实现方式有哪些?

需求背景 环境 MySQL 版本: 5.7.20-log 开发规范 公司后端开发规范有这么一点: 更新数据库表中数据的时候,不允许先删,然后批量插入 需要将入参与表中数据比判断,找出哪些是新插入,哪些需要更新,哪些是删除的,然后再做对应的数据操作 需求 我们有表如下: 当商品配送完后之后,需要记录它的最新配送价,若商品最新配送价已经存在则进行更新,不存在则执行插入 针对这个需求,我们有哪些实现方式? 代码处理 按开发规范中说的处理 通过代码在内存中进行数据处理,找出插入列表与更新列表,然后执

老男孩的架构路 1278

“傻瓜”stata入门——熟悉操作界面

小白开始学习stata和matlab,零基础上手

nnpersistent的博客 4461

Mybatis基于Oracle数据库实现批量插入更新的几种方式

【代码】Mybatis基于Oracle数据库实现批量插入的几种方式。

林夕 9749

从零构建知识图谱:本体工程与知识建模实战指南

本文是一份从零构建知识图谱的实战指南,重点阐述了知识建模与本体工程的核心地位。文章详细介绍了如何使用Protégé工具进行本体设计,包括定义领域、复用现有本体、构建类层次与属性约束,并讲解了如何利用D2RQ工具将关系数据库数据转换为RDF三元组,最终通过Apache Jena Fuseki搭建SPARQL端点进行知识查询与推理,让知识图谱真正“活”起来。

weixin_29057695的博客 573

K-近邻算法: k-nearest neighbor classification (kNN) 详细介绍

@创建于:2020.04.09 @修改于:2020.04.09,2024.06.22 文章目录1、kNN介绍2、k近邻法的三要素详解2.1 距离度量2.2 k值的选择2.3 分类决策规则3、k近邻法的实现:kd树4、Python实现过程5、优缺点6、常见问题 1、kNN介绍 kNN是一个基本而简单的分类算法,作为监督学习,那么KNN模型需要的是有标签的训练数据,对于新样本的类别由与新样本距离最近的k个训练样本点按照分类...

chenhepg的博客 9万+

mysql 插入更新一条sql 搞定

插入数据时,我们经常会遇到这样的情况:1、首先判断数据是否存在;2、如果不存在,则插入;3、如果存在,则更新。 在SQL Server中可以这样处理: if not exists (select 1 from t where id = 1)? insert into t(id, update_time) values(1, getdate()) else update t s

Smile李先生 3656

127.手机防回滚机制(Anti-Rollback)详解|efuse熔丝原理与降级绕过方案

手机刷机与维修技术指南 本文针对电子工程师和维修人员,详细介绍了主流手机品牌(高通、联发科、苹果)的刷机原理与维修方法。内容包括三大平台的刷机协议差异、关键操作模式(Fastboot/DFU/EDL)、分区表修复等核心技术,并提供了完整的Python自动化刷机脚本。文章重点解决系统崩溃、基带丢失、分区损坏等常见故障,涵盖从环境搭建到具体操作的全流程指导,所有指令均经过实测验证。适用于专业维修场景下的跨品牌设备修复需求。

2601_95651806的博客 295

Mybatis 实现MySQL的:无数据插入,有数据更新

一、插入一条数据,存在则忽略,不存在就插入(必须现有唯一键) 使用insert ignore语句: insert ignore into table(col1,col2) values ('value1','value2'); 例如插入数据: insert ignore into user_info (last_name,first_name) values ('liu','yazhua...

liinux-Talk is cheap,show me the code. 2850

修复 Outlook 中缺少的 Microsoft Teams 插件

在 Outlook 中缺少 Microsoft Teams 插件可能会限制您在邮件客户端中直接访问 Microsoft Teams 的功能。在本文中,我将向您介绍一些修复缺少 Microsoft Teams 插件的方法,并提供相应的源代码。如果问题仍然存在,请确保您的 Outlook 和 Microsoft Teams 都是最新版本,并尝试在不同的计算机上进行测试。在实际使用中,请根据您的需求进行适当的修改和调整。如果前面的步骤未能解决问题,您可以尝试重新安装 Microsoft Teams 插件。

CodeWWWCode的博客 5674

OV9734 DATASHEET,完整规格书

OV9734完整规格书,包括电气参数,尺寸规格,寄存器配置表等

Mybatis批量插入到MySQL和Oracle

通过Mybatis向MySQL和Oracle中批量更新数据对比

weixin_49778600的博客 972

TMS_WebGMaps_1.9.8.0_Full_Source

TMS_WebGMaps_1.9.8.0_Full_Source 全源码 delphi cbuilder

mysql 批量插入和批量更新的

做法二: 利用mysql的insert into 表名 values (.....),(.....),(.....),(.....) 特性一次性大批量的插入数据,提高效率 [java] 备注:我用的是ibatis 业务层: private void batchInsertAct(ListProgramDetail> acts) throws DataBaseExcepti

ahzxj2012的专栏 914

RU DOS版、EFI版32bit/64bit BIOS常用工具

DOS版、EFI版的RU工具,可用来查看IO、MEM、PCI、SMBIOS等资源。亲测在EFI SHELL下可用。

华为的C语言编程规范.pdf

华为技术有限公司内部技术规范。本规范制定了编写C语言程序的基本原则、规则和建议。从代码的清晰、简洁、可测试、安全、程序效率、可移植各个方面对C语言编程作出了具体指导。

上一篇: “他”靠这份GitHub star过万的1121页图解算法成功杀进字节跳动!
下一篇: CAP理论,我拆开来细讲,用例子展现,够清晰了吧!
加班不秃顶
博客等级 码龄7年 1822粉丝 599原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值