MySQL ON DUPLICATE KEY UPDATE 批量插入并按条件更新MySQL ON DUPLICATE K

MySQL ON DUPLICATE KEY UPDATE 用在需要批量插入且按照主键或唯一索引批量更新的场景，可以显著提高更新速度，避免需要循环判断。

使用场景示例：A字段相同（唯一或联合唯一），且B字段符合某条件的时候，更新B、C等字段为最新值。

比如，某业务场景下，字段A（消息内容 content），B（发送时间 send_time），A会大量重复出现但只需记录一条，没出现过就新插入，出现过需要判断B如果比当前值大（即发送时间更晚），就更新B和其它字段。

这种场景下，因为更新的字段值是动态的，如果不用ON DUPLICATE KEY UPDATE，会很难实现批量更新操作。但是ON DUPLICATE KEY UPDATE有几个注意事项，用不对就会出现奇怪的结果。

如果需要限制更新条件，只能用IF函数，不支持WHERE语句；
如果某字段本身也被更新，该字段用来做多个IF条件时，要先把结果赋值给自定义变量，然后用这个变量做后续的判断。否则因为该字段在进行前面的更新时可能已经被修改了，再用它做后续判断已经失效了；
更新字段的顺序非常重要，是按先后顺序执行的；

示例：

-- 建表
CREATE TABLE message (
 content_id VARCHAR ( 32 ) COMMENT '消息内容id', 
 send_time BIGINT ( 10 ) COMMENT '消息发送时间', 
 update_time BIGINT ( 10 ) COMMENT '更新时间', 
PRIMARY KEY ( content_id ) );

-- 插入测试数据（为方便观察结果，用了很简单的数据）
INSERT INTO message
  (content_id, send_time, update_time)
VALUES
  ('123456', 123, 456)

-- 插入主键重复的数据
INSERT INTO message
  (content_id, send_time, update_time)
VALUES
  ('123456', 1234, 4567)
ON DUPLICATE KEY UPDATE
  send_time = IF(send_time < VALUES(send_time), VALUES(send_time), send_time),
  update_time = IF(send_time < VALUES(send_time), VALUES(update_time), update_time);

注意：以上语句可以正常执行，但数据会更新为('123456', 1234, 456)，update_time并没有被更新。原因是按照顺序执行完send_time = IF(send_time < VALUES(send_time), VALUES(send_time), send_time)之后，send_time本身已经被重新赋值了，执行下一句IF(send_time < VALUES(send_time)时已经不满足了。

解决方案1：把有可能被修改的字段放在最后更新。

-- 插入主键重复的数据
INSERT INTO message
  (content_id, send_time, update_time)
VALUES
  ('123456', 12345, 45678)
ON DUPLICATE KEY UPDATE
  -- 更换了执行顺序
  update_time = IF(send_time < VALUES(send_time), VALUES(update_time), update_time),
  send_time = IF(send_time < VALUES(send_time), VALUES(send_time), send_time);

解决方案2（推荐）：把判断条件的结果赋值给一个自定义变量。

-- 插入主键重复的数据
INSERT INTO message
  (content_id, send_time, update_time)
VALUES
  ('123456', 12345, 45678)
ON DUPLICATE KEY UPDATE
  -- 条件结果赋值给变量
  send_time = IF(@update_record := send_time < VALUES(send_time), VALUES(send_time), send_time),
  update_time = IF(@update_record, VALUES(update_time), update_time);

两种方案都会得到预期的结果('123456', 12345, 45678)。

方案2要注意的是，@update_record变量的赋值依然要放在最前面的字段。

另外：一些orm工具（比如golang的gorm）最终生成的sql语句（有可能）会重新组装字段顺序（比如按照字段首字母排序），并不一定是按照代码里写的顺序，这时候要把变量的赋值放在首字母排序最靠前的字段，才能达到最终生成的sql语句变量的赋值在最前面。

end.