How to No-op if a duplicate key exception occurs?

Question

I know I can use the ON DUPLICATE KEY UPDATE and then do a no-op operation in MySQL if a duplication exception occurs, but is there a more efficient way?

Answer 1

While that is true, I've found that doing INSERT IGNORE creates holes in your auto increment ids. Specifically, if you do an INSERT IGNORE and it collides with a current row, no data is written (what you want), BUT the auto_increment value for that table gets incremented by one.

If you're narcotic like me, holes in auto increment columns kill you. So I tend to not do that. I just do something like:

INSERT INTO TABLE (column list) VALUES (value list) ON DUPLICATE KEY UPDATE random_column = random_column

This successfully does a no-op. It's purely a matter of style, but I think this method is cleaner.

Answer 2

INSERT IGNORE INTO tableName ....

see the Docs