From time to time I get lazy and use
REPLACE INTO instead of checking for records and then only inserting the ones that don’t exist. It works really well for importing records where some records might be already in the database. Today, I ran into a little snag due to my laziness.
MySQL uses the following algorithm for
LOAD DATA ...):
- Try to insert the new row into the table
- While the insertion fails because a duplicate-key error occurs for a primary key or unique index:
- Delete from the table the conflicting row that has the duplicate key value
- Try again to insert the new row into the table
When you combine this with foreign keys that are defined with
ON DELETE CASDCADE you end up nuking child records when you
REPLACE INTO a parent record. It only took me an hour and a half to debug this insanity.