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。