MySQL 8.0 匹配id仅更新更大日期值且不新增行的操作方法

实现代码 #

你可以直接使用MySQL 8.0支持的JSON_TABLE函数解析传入的JSON数据，关联原表做条件更新，无需插入新行，符合你的所有要求：

UPDATE contacts c
JOIN JSON_TABLE(
  '[
    {"id": 1,"date": "2020-01-01 13:00:00"},
    {"id": 2,"date": "2020-01-01 14:00:00"},
    {"id": 3,"date": "2020-01-01 14:00:00"},
    {"id": 5,"date": "2020-01-01 14:00:00"}
  ]',
  '$[*]' COLUMNS (
    update_id INT PATH '$.id',
    update_date DATETIME PATH '$.date'
  )
) AS update_data
ON c.id = update_data.update_id
SET c.date = update_data.update_date
WHERE 
  c.date IS NULL 
  OR update_data.update_date > c.date;

执行逻辑说明 #

• 无匹配id的行（比如示例中的id=5）：关联不到原表数据，不会触发更新也不会插入
• id匹配但传入date小于等于原有date（比如示例中的id=1）：不满足WHERE条件，不更新
• id匹配且原有date为null（比如示例中的id=2）：满足WHERE条件，直接更新为传入值
• id匹配且传入date大于原有date（比如示例中的id=3）：满足WHERE条件，更新为传入值
• 不在更新数据范围内的行（比如示例中的id=4）：完全不受影响
  执行后结果和你给出的预期结果完全一致。

替代方案（适用于程序拼接SQL场景） #

如果你的业务场景需要拼接SQL而不是直接传入JSON，可以用CASE语法实现同等效果：

UPDATE contacts
SET `date` = CASE id
  WHEN 2 THEN '2020-01-01 14:00:00'
  WHEN 3 THEN '2020-01-01 14:00:00'
  ELSE `date`
END
WHERE id IN (1,2,3,5)
AND (
  `date` IS NULL 
  OR (id=1 AND '2020-01-01 13:00:00' > `date`)
  OR (id=2 AND '2020-01-01 14:00:00' > `date`)
  OR (id=3 AND '2020-01-01 14:00:00' > `date`)
  OR (id=5 AND '2020-01-01 14:00:00' > `date`)
);

内容的提问来源于stack exchange，提问作者MegaMathias