While performing an Place statement with lots of rows, I wish to skip duplicate records that will otherwise cause failure. After a little research, my options seem to be using either:

  • "ON DUPLICATE KEY UPDATE" which suggests a pointless update at some cost, or
  • "Place IGNORE" which suggests an invite for other sorts of failure to slide in surprise.

Am I in these presumptions? What's the easiest method to simply skip the rows that may cause replicates and merely continue onto another rows?

I would suggest using Place...ON DUPLICATE KEY UPDATE.

If you are using Place IGNORE, then your row will not really be placed whether it produces a duplicate key. However the statement will not generate a mistake. It creates an alert rather. These cases include:

  • Placing a replica type in posts with PRIMARY KEY or UNIQUE constraints.
  • Placing a NULL right into a column having a NOT NULL constraint.
  • Placing a row to some partitioned table, however the values you place don't map to some partition.

If you are using REPLACE, MySQL really does a Remove then an Place internally, that has some unpredicted unwanted effects:

  • A brand new auto-increment ID is allotted.
  • Dependent rows with foreign secrets might be erased (if you are using cascading down foreign secrets) otherwise avoid the REPLACE.
  • Triggers that fire on Remove are performed unnecessarily.
  • Unwanted effects are propagated to replication slaves too.

correction: both REPLACE and Place...ON DUPLICATE KEY UPDATE are non-standard, proprietary inventions specific to MySQL. ANSI SQL 2003 defines a MERGE statement that may solve exactly the same need (and much more), but MySQL doesn't offer the MERGE statement.

I routinely use Place IGNORE, also it seems like precisely the type of behavior you are searching for too. As lengthy you may already know that rows which may cause index conflicts won't be placed and also you plan your program accordingly, it should not cause any problem.

Something vital that you add: When utilizing Place IGNORE and you have key violations, MySQL doesn't raise an alert!

By trying for example to place 100 records at any given time, with one faulty one, you can get in interactive mode:

Query OK, 99 rows affected (.04 sec)

Records: 100 Replicates: 1 Alerts:

As you can see: No Alerts! This behavior is even wrongly referred to within the official Mysql Docoumentation.

In case your script must be informed, if some records haven't been added (because of key violations) you need to call mysql_info() and parse it for that "Replicates" value.

ON DUPLICATE KEY UPDATE isn't really within the standard. It comes down to as standard as REPLACE is. See SQL MERGE.

Basically both instructions are alternative-syntax versions of normal instructions.

I understand this really is old, but I'll add this note just in case other people (much like me) gets to this site while looking for info on Place..IGNORE.

As pointed out above, if you are using Place..IGNORE, errors that occur while performing the Place statement are treated as alerts rather.

One factor which isn't clearly pointed out is the fact that Place..IGNORE may cause invalid values is going to be modified towards the nearest values when placed (whereas invalid values would make the query to abort when the IGNORE keyword wasn't used).

Replace Into appears as an option. Or seek advice from Otherwise Is available(QUERY) Then Place This can place or remove then place. I am inclined to get a Otherwise Is available check first.