MySQL single query method to insert records only when they don't exist

[JumpingMountain]

I’m working with a large MySQL table that has about 14 million rows. I need to add new data but want to make sure I don’t create duplicate entries.

Right now I’m thinking about using two separate queries - first checking if the record exists, then inserting if it doesn’t. But this seems inefficient and I’m wondering if there’s a better way.

I know I can set a unique constraint on a column, but when I try to insert a duplicate record through my PHP script, it throws an error and stops execution. Is there a way to handle this more gracefully?

What’s the best approach to insert data only when it doesn’t already exist, using just one MySQL query instead of multiple database calls?

[Gizmo_Funny]

You could also use REPLACE INTO instead of INSERT. It deletes any existing row with the same unique key and inserts the new one. The syntax is simple: REPLACE INTO your_table (column1, column2) VALUES ('value1', 'value2'). Just watch out - it actually does a DELETE then INSERT when it finds duplicates. That means auto-increment values change and any columns you don’t specify get reset to defaults. With your 14 million row table, this could hurt performance if you’re replacing lots of existing records. It’s best when you want to completely overwrite duplicates, not just skip them.

[JumpingRabbit]

INSERT IGNORE is what you want. It lets MySQL skip rows that would create duplicate keys instead of throwing an error and stopping. Just use: INSERT IGNORE INTO your_table (column1, column2) VALUES ('value1', 'value2'). You’ll need a unique index or primary key on your columns to catch duplicates. I’ve used this on similar table sizes and it’s fast. Heads up though - INSERT IGNORE skips ALL errors, not just duplicates. If you need more control, try ON DUPLICATE KEY UPDATE to specify what happens when it hits a duplicate.

[MarkSeeker91]

you can also use INSERT … ON DUPLICATE KEY UPDATE with identical values. something like INSERT INTO table (col1, col2) VALUES ('val1', 'val2') ON DUPLICATE KEY UPDATE col1=col1. it’ll skip duplicates without throwing errors like a normal insert would. works great for bulk inserts too.