MySQL bulk INSERT .. ON DUPLICATE KEY UPDATE 同时指定单独的列

Question

如果一个表来自具有不同数据集的 2 个不同的 cronjobs，我需要批量插入数千行，我目前正在使用 INSERT .. ON DUPLICATE KEY UPDATE

考虑到第一个值是检查重复项的唯一键，

例如，第一个 cronjob 将运行(从数据源 1):

INSERT INTO `places` () VALUES
 ('aaa', 'bbb', NULL),
 ('ccc', NULL, 'ddd')
ON DUPLICATE KEY UPDATE;

例如，第二个 cronjob 将运行(来自数据源 2):

INSERT INTO `places` () VALUES
 ('aaa', NULL, 'eee'),
 ('ccc', 'ddd', 'fff')
ON DUPLICATE KEY UPDATE

如您所见，第二个 cronjob 会用 null 值覆盖第一个 cronjob 的有效值，这是我不想要的。
如果我从数据中删除 null 值，查询就会中断，因为数据不匹配。

我能想到的唯一解决方案是执行大量的单个插入/更新，这会更慢。

如果我能批量生产

INSERT INTO `places` VALUES
 (column1='aaa', column3='eee'),
 (column1='ccc', column2='ddd', column3='fff')
ON DUPLICATE KEY UPDATE

那么我就不必担心其他任何人的 key ，但我认为这在 MySQL 中是不可能的。有没有人知道更好的方法来做到这一点？

Answer 1

我测试了以下内容，它似乎有效:

INSERT INTO `places` (col1, col2, col3) VALUES
 ('aaa', NULL, 'eee'),
 ('ccc', 'ddd', 'fff')
ON DUPLICATE KEY UPDATE
 col1=COALESCE(VALUES(col1), col1),
 col2=COALESCE(VALUES(col2), col2),
 col3=COALESCE(VALUES(col3), col3);

在 ON DUPLICATE KEY 语句中，VALUES(<col>)意思是“无论我试图在每一行中插入什么。”所以在上面的第一行中，VALUES(col2)为 NULL，并且 col2是已经存储在该主键的行中的值。

COALESCE()函数返回它的第一个非 NULL 参数。所以COALESCE(NULL, col2)返回 col2 的现有值.

如果您希望以这种方式让它们“默认为上一个”，您确实需要写出所有这些列的所有子句。

P.S.:您对 INSERT INTO places () 的使用是不正确的。列的空列表 ()必须对应于一个空的值列表。 IE。这两个列表必须具有相同数量的术语。