代码之家 › 专栏 › 技术社区 › curiousgeorge

在具有单独更新列的重复密钥更新批上插入

batch-query query-optimization insert sql mysql

curiousgeorge · 技术社区 · 7 年前

我有一张桌子 (col1 (pk), col2, col3, col4) 我需要将一组记录插入到表中,并在重复键上更新它们。我想做一个批量查询来加快速度。然而 col4 没有 NOT NULL 限制当我想用记录更新时,问题就出现了 (val1, val2, val3, None), (val4, val5, val6, val7) 。对于第一条记录,我不想更新第4列(如果 (val1, val2, val3, val8) 存在于DB中,我不想重写val8,因为与显式设置为Null相比,None表示缺少值)。然而,对于第二条记录,我想更新 col4 因为传递了显式值。如果有一条记录,我只需将更新列设置为 col2, col3, 而不是 col4 ,但我想批处理此查询,并且需要 col4 为其传递值时更新,没有值时不更新。从逻辑上讲,我需要下面给出的东西。

INSERT INTO table1
  (col1, col2, col3, col4)
VALUES
  ('val1', 'val2', 'val3'), ON DUP KEY UPDATE col2, col3
  ('val5', 'val6', 'val7', 'val8'), ON DUP KEY UPDATE col2, col3, col4
  ('val9', 'val10', 'val11') ON DUP KEY UPDATE col2, col3

很明显,这可以通过让它成为一系列单独的语句来实现,但我想找到一种方法来批处理它。是否有任何方法可以在sql中实现这一点或其他方法?

3 回复 | 直到 7 年前

Andomar 7 年前

在 on duplicate key update 插入的一部分,可以使用 values 。您可以使用 coalesce 在以下情况下保留更新前值: null :

INSERT INTO YourTable (col1, col2, col3, col4) VALUES
    ('val1', 'val2', 'val3', null)
,   ('val5', 'val6', 'val7', 'val8')
,   ('val9', 'val10', 'val11', null)
ON DUPLICATE KEY UPDATE
    col1 = values(col1)
,   col2 = values(col2)
,   col3 = values(col3)
,   col4 = coalesce(values(col4), col4)

Example on SQL Fiddle.

在回复您的评论时,您可以设置 无效的 显式使用 case :

,   col4 = case values(col4) 
           when 'None' then null 
           else coalesce(values(col4), col4)
           end

这里的明显风险是,您不能再更新为“无”:

Paul Spiegel 7 年前

“值”中的字段数必须与“插入”中的字段数相同。但你可以通过 NULL 对于 col4 并在更新部分使用COALESCE。

INSERT INTO table1
  (col1, col2, col3, col4)
VALUES
  ('val1', 'val2',  'val3',  NULL),
  ('val5', 'val6',  'val7',  'val8'),
  ('val9', 'val10', 'val11', NULL)
ON DUPLICATE KEY UPDATE
  col2 = VALUES(col2),
  col3 = VALUES(col3),
  col4 = COALESCE(VALUES(col4), col4)

Alexey 7 年前

这就是你要找的东西吗?

INSERT INTO table1
  (col1, col2, col3, col4)
VALUES
  ('val1', 'val2', 'val3', null)
  ('val5', 'val6', 'val7', 'val8')
  ('val9', 'val10', 'val11', null)
ON DUPLICATE KEY UPDATE
  col2 = values(col2),
  col3 = values(col3),
  col4 = coalesce(values(col4), col4)
;