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。