MySQL如何实现仅source=2时触发ON DUPLICATE KEY UPDATE更新

可以单条SQL实现该需求。MySQL的ON DUPLICATE KEY UPDATE语法不支持直接追加WHERE子句，你可以通过IF()条件函数实现按条件更新的逻辑。

插入source为1的SQL写法 #

INSERT INTO routes (id, train, operator, source) 
VALUES ('$idDate', '$id', '$operator', '1')
ON DUPLICATE KEY UPDATE 
    train = IF(source = 2, '$id', train),
    source = IF(source = 2, '1', source)

注：id为唯一键，触发重复冲突时id本身就是相同值，不需要额外更新id字段，原写法中的id='$idDate'属于无效操作可以省略。

插入source为2的SQL写法 #

INSERT INTO routes (id, train, operator, source) 
VALUES ('$idDate', '$id', '$operator', '2')
ON DUPLICATE KEY UPDATE 
    train = IF(source = 2, '$id', train),
    source = IF(source = 2, '2', source)

逻辑说明 #

• 触发唯一键冲突时，IF(source = 2, 新值, 旧值)会先判断原有冲突行的source字段值：
  • 若原有source等于2，字段更新为新插入的对应值
  • 若原有source不等于2，字段保留原有值，相当于没有执行更新操作
• 执行后可以通过SQL返回的影响行数判断执行结果：返回1为插入新行，返回2为符合条件执行了更新，返回0为冲突但不符合更新条件无操作，刚好匹配你的业务判断需求。

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