摘自官方文档的一段
Because the results of INSERT … SELECT statements depend on the ordering of rows from the SELECT and this order cannot always be guaranteed, it is possible when logging INSERT … SELECT ON DUPLICATE KEY UPDATE statements for the source and the replica to diverge. Thus, INSERT … SELECT ON DUPLICATE KEY UPDATE statements are flagged as unsafe for statement-based replication. Such statements produce a warning in the error log when using statement-based mode and are written to the binary log using the row-based format when using MIXED mode. An INSERT … ON DUPLICATE KEY UPDATE == statement against a table having more than one unique or primary key is also marked as unsafe. ==(Bug #11765650, Bug #58637)
链接
https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html
意思是说:
如果一个表有多个唯一键或者主键的时候不安全.
实测:如果更新的字段是第二个值仍然是唯一约束 则还是报错
ON DUPLICATE KEY UPDATE
sn_1 = values(sn_1),
sn_2 = values(sn_2),
add_time = values(add_time)
例如上面sql需要更新2个字段
如果两个序列号均有唯一约束
则第一个个序列号重复的时候能更新后面的值
如果第二个序列号重复 则无法更新