头图

Mysql 数据库的批量插入或更新(Upsert)

这个问题已经困扰我一段时间了,对于大量数据的插入或更新,批量操作肯定比每条记录调用一次快得多,新数据可以用 insert 批量插入,老数据可以用 replace into 批量更新。但如果不知道数据是否存在(是否有唯一key和数据库中已有记录重复)想在一批数据库中,插入新记录,更新老记录怎么办?
之前甚至想过封装一个函数,先用 select ... in 批量查询,然后分两组插入和更新,但一方面通用性不佳,另一方面这不是一个原子操作,对于并发的情况,有可能查询的时候记录不存在,插入的时候就已经存在了。
仔细 google 了一下,才发现这种“存在update,不存在insert”的动作,有个专有名词,叫做“upsert”,相当形象。解决方案呢,不同数据库各有自己的解决方案和方言,Mysql 叫做 on duplicate key update,PostgreSql 中叫做 on confict do update。

基本用法

INSERT INTO t1 (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;

假设a、b是联合主键或者唯一索引,上面这句话意味着,当数据库中不存在 a=1 且 b=2 的记录,就插入一条 a=1,b=2,c=3 的新记录;如果存在,就把原记录的字段 c,更新为 c+1。如果 a 是单独的组成唯一键字段,那么判断是否存在的时候,就只考虑字段a,如果 a=1 的记录存在,也只会更新字段 c,忽略字段 b。

复用数值

通常情况下,我们 update 的字段,会比 insert 少一点,但是数据是一样的,这时候,再写一遍 values,不但显得多余,而且容易出错,那么就可以用下面的语法:

INSERT INTO t1 (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c = VALUES(c);

于是insert 或 update 字段 c ,结果就是同一个值了。

批量处理

回到最初的问题,刚才这句话如果只能支持一条记录,还是意义不大,好在它像 insert 一样支持批量处理:

INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) AS new
  ON DUPLICATE KEY UPDATE c = VALUES(c);

这样在代码中,就可以拼接多条记录的 SQL 语句,一次性完成 upsert 操作了。
注意:在 mysql 语法上,对于一次 upsert 插入多少条记录,没有任何限制,上限完全取决于客户端和数据库端操作系统,对字符串的长度要求;不过,考虑到网络传输的包大小,建议不要太长,通常认为一条语句不能超过 500 MB 的大小。

至于在 Python 语言中,如何实现 upsert ,请看一下篇。


Update

仅当记录不存在的时候插入

有的时候,我们需要在记录不存在的情况下插入记录,而存在的情况下,就什么都不做,这时 ON DUPLICATE KEY UPDATE 就不合适了,它必须要更新点什么,此时可以用 INSERT IGNORE INTO 语句。例如:

INSERT IGNORE INTO t1 (a,b,c) VALUES (1,2,3)

如果数据库中已有相关记录,那么不会报错,只是返回:

Query OK, 0 rows affected (0.00 sec)

Update 2023-3-4
发现了ON DUPLICATE KEY UPDATE 语句一个问题,就是无论是否新插入数据,所有要处理的数据集,都会消耗自增 id 数字。比如传了 1000 条数据记录,其中 999 条都是存在的,只有 1 条是新增,那么执行完之后,也会发现自增 id 的当前值,增加了 1000!!!。

在数据量小的时候,这也没什么,本来自增 id 也不保证连续;但当 upsert 的数据量非常大,而绝大部分都是已经存在的数据时,自增数值的浪费就是惊人的。我们在业务上就出现过每次 upsert 约 2千万条数据,其中只有几百条新增,但 id 值已经上涨了 2千万;而主键 id 字段的数据类型又是 int,很快就要突破上限,要快速解决只能修改字段类型为 BigInt。


hawk
关注创业公司的技术与团队
289 声望
20 粉丝
0 条评论
推荐阅读
为什么 AI 时代,人人都需要学一点编程
经常有人调侃说,ChatGPT 来了,你们这些码农和程序员都要失业了,这当然首先是在吸引眼球,有流量才有钱么。这些做自媒体的up主是不会认真想想,AI 到底对编程造成了怎样的冲击,对码农们的影响到底是是什么?真...

songofhawk阅读 596

封面图
花了几个月时间把 MySQL 重新巩固了一遍,梳理了一篇几万字 “超硬核” 的保姆式学习教程!(持续更新中~)
MySQL 是最流行的关系型数据库管理系统,在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System:关系数据库管理系统)应用软件之一。

民工哥14阅读 2.1k

封面图
硬卷完了!MongoDB 打怪升级进阶成神之路( 2023 最新版 )!
前面我们学习:MySQL 打怪升级进阶成神之路、Redis 打怪升级进阶成神之路,然后我们还在继续 NoSQL 的卷王之路。从第一篇文章开始,我们逐步详细介绍了 MogoDB 基础概念、安装和最基本的CURD操作、索引和聚合、工...

民工哥7阅读 776

封面图
初学后端,如何做好表结构设计?
这篇文章介绍了设计数据库表结构应该考虑的4个方面,还有优雅设计的6个原则,举了一个例子分享了我的设计思路,为了提高性能我们也要从多方面考虑缓存问题。

王中阳Go4阅读 1.8k评论 2

封面图
MySQL百万数据深度分页优化思路分析
一般在项目开发中会有很多的统计数据需要进行上报分析,一般在分析过后会在后台展示出来给运营和产品进行分页查看,最常见的一种就是根据日期进行筛选。这种统计数据随着时间的推移数据量会慢慢的变大,达到百万...

一个程序员的成长7阅读 984

封面图
Vue+Express+Mysql全栈项目之增删改查、分页排序导出表格功能
本文记录一下实现一个全栈项目,前端使用vue框架、后端使用express框架、数据库使用mysql。此项目的意义不仅仅有助于我们复习nodejs相关知识、更有助于带前端新人,使其快速从整体全局角度中,理解常规后台管理系...

水冗水孚4阅读 2.7k

深入理解MySQL索引底层数据结构
在日常工作中,我们会遇见一些慢SQL,在分析这些慢SQL时,我们通常会看下SQL的执行计划,验证SQL执行过程中有没有走索引。通常我们会调整一些查询条件,增加必要的索引,SQL执行效率就会提升几个数量级。我们有没...

京东云开发者3阅读 616

封面图
289 声望
20 粉丝
宣传栏