共计 1088 个字符,预计需要花费 3 分钟才能阅读完成。
这个问题曾经困扰我一段时间了,对于大量数据的插入或更新,批量操作必定比每条记录调用一次快得多,新数据能够用 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,请看一下篇。