Search code examples
mysqlsqletltalend

Both “INSERT IGNORE” and “… ON DUPLICATE KEY UPDATE” what happens?

What will mysql do with both INSERT IGNORE and … ON DUPLICATE KEY UPDATE ?

This is not a question about their differences. I ask because Talend ETL does this behind the UI and I'm worried it will have side effects especially if I don't want to update and do something like this:

String insertIgnore_tMysqlOutput_10 = "INSERT IGNORE INTO `"
+ "Employees"
+ "` (`Name`,`JobTitle`) VALUES (?,?) ON DUPLICATE KEY UPDATE `Name` = ?";
Solution

  • IGNORE just acts as a kind of error-suppressor, making fatal errors act as warnings instead.

    ON DUPLICATE KEY UPDATE doesn't trigger an error, so INGORE has no effect on it.

    Therefore, IGNORE has no effect on duplicate keys when ON DUPLICATE KEY UPDATE is used as well. However, if a different error were to occur then IGNORE would indeed have an effect.