Avoid Duplicates of Unique Key Within INSERT Query

Question

I have a MySQL query that looks like this:

INSERT INTO beer(name, type, alcohol_by_volume, description, image_url) VALUES('{$name}', {$type}, '{$alcohol_by_volume}', '{$description}', '{$image_url}')

The only problem is that name is a unique value, which means if I ever run into duplicates, I get an error like this:

Error storing beer data: Duplicate entry 'Hocus Pocus' for key 2

Is there a way to ensure that the SQL query does not attempt to add a unique value that already exists without running a SELECT query for the entire database?

Answer 1

You could of course use INSERT IGNORE INTO, like this:

INSERT IGNORE INTO beer(name, type, alcohol_by_volume, description, image_url) VALUES('{$name}', {$type}, '{$alcohol_by_volume}', '{$description}', '{$image_url}')

You could use ON DUPLICATE KEY as well, but if you just don't want to add a row INSERT IGNORE INTO is a better choice. ON DUPLICATE KEY is better suited if you want to do something more specific when there are a duplicate.

If you decide to use ON DUPLICATE KEY - avoid using this clause on tables with multiple unique indexes. If you have a table with multiple unique indexes ON DUPLICATE KEY-clause could be giving unexpected results (You really don't have 100% control what's going to happen)

Example: - this row below only updates ONE row (if type is 1 and alcohol_by_volume 1 (and both columns are unique indexes))

ON DUPLICATE KEY UPDATE beer SET type=3 WHERE type=1 or alcohol_by_volume=1

To sum it up:

ON DUPLICATE KEY just does the work without warnings or errors when there are duplicates.

INSERT IGNORE INTO throws a warning when there are duplicates, but besides from that just ignore to insert the duplicate into the database.