Fun MySQL fact of the day:
IGNORE at your own peril
Yesterday, we saw how
INSERT ... ON DUPLICATE KEY UPDATE can unknowingly result in not-completely-expected situations. To get around that, you may have thought to use
INSERT IGNORE, instead. Often,
INSERT IGNORE is used when attempting to insert a row that may already exist. The underlying expectation a developer often has is that
INSERT IGNORE means "do nothing if the row already exists, otherwise, insert the row". And, well,
INSERT IGNORE definitely does that ... and more.
INSERT IGNORE really means "treat errors as warnings" (did you know you can
SHOW WARNINGS?). Uh oh.
Right. Let's say you have a table with a
phrase VARCHAR(10) column and you use
INSERT IGNORE to insert a phrase like
'this is a really long message'. You may expect to get an error like
Data truncated for column 'phrase' at row 1. But you won't. Instead, MySQL will truncate the value to 10 characters, insert the row, and pretend everything is fine.
And this isn't just String types: it will figure out the closest approximation of a valid value on your behalf. So,
2019-03-15 24:00:00? Didn't you mean
0000-00-00 00:00:00? A
TINYINT value of
128? I think you mean
127. And the fun doesn't stop there! You can
DELETE IGNORE, and even
CREATE TABLE ... SELECT IGNORE.
My advice? Same as yesterday: instead of using
INSERT IGNORE, do a
SELECT (preferably on a replica) and
UPDATE by primary key accordingly, safely handling the rare duplicate key error in your code.