Avoid duplicate unique key in INSERT request

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 the name is a unique value, which means that if I ever came across duplicates, I get this error:

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

Is there a way to make sure that the SQL query is not trying to add a unique value that already exists without executing a SELECT query for the entire database?

Answer 1

Of course, you can use INSERT IGNORE INTO, for example:

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

You can also use ON DUPLICATE KEY , but if you just don't want to add the INSERT IGNORE INTO , this is the best choice. ON DUPLICATE KEY better suited if you want to do something more specific when there is a duplicate.

If you decide to use ON DUPLICATE KEY , avoid using this clause in tables with several unique indexes. If you have a table with several unique ON DUPLICATE KEY -clause may give unexpected results (you really don't have 100% control over what happens)

Example: - this row below updates only one row (if the 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 

Summarizing:

ON DUPLICATE KEY just does the job without warning or error if there are duplicates.

INSERT IGNORE INTO gives a warning when there are duplicates, but other than that it is simply ignored to insert the duplicate into the database.

Answer 2

As it happens, in MySQL there is a way to use ON DUPLICATE KEY UPDATE . This is available since MySQL 4.1

 INSERT INTO beer(name, type, alcohol_by_volume, description, image_url) VALUES('{$name}', {$type}, '{$alcohol_by_volume}', '{$description}', '{$image_url}') ON DUPLICATE KEY UPDATE type=type; 

You can also use INSERT IGNORE INTO... as an alternative, but the operator will still issue a warning (although instead of an error).

Answer 3

Yes there is. You can use the ON DUPLICATE KEY clause of the mysql INSERT . The syntax is explained here.

 INSERT INTO beer(name, type, alcohol_by_volume, ...) VALUES('{$name}', {$type}, '{$alcohol_by_volume}', ...) ON DUPLICATE KEY UPDATE type={$type}, alcohol_by_volume = '{$alcohol_by_volume}', ... ; 

Answer 4

Pretty simple - your code should figure out what it wants to do if something tries to insert a duplicate name. So you need to execute the select statement first:

  SELECT * FROM beer WHERE name='{$name}' 

And then run the if statement to determine if you have a result.

if the results = 0, then go and run your insert. Also ... whatever you do. Report an error to the user? Change the database differently? Ignore this completely? How does this insert statement appear? Bulk update from file? User input from a web page?

How you achieve this insert statement, and how it should affect your workflow, you need to determine exactly how you handle this "else." But you should definitely handle this.

But just make sure that the select and insert statements in the transaction are together so that other people in the same stuff are not a problem.

Answer 5

Yes, first selecting a name from the database, and if the query result is not zero (zero records), then the name already exists, and you need to get a different name.