Tuesday, April 24, 2012

How to Deal with Duplicate Primary Keys on MySQL

Recently, I came across with a problem where I had to copy and insert some MySQL data from one database to another.
The issue regarded the same table structure in both databases, but with different data content in each. It was vital not to lose any information when importing the dump from one table to the other.
One of the most important details was that it was possible to have the same tuple repeated in both tables and I could not afford overwriting any content.

So, here follows the command used:

mysqldump -u user -p  --no-create-info --insert-ignore mydatabase mytable > file.dump

The --no-create-info makes sure no DROP or CREATE info are added, so your table is not deleted and recreated and you don't lose any information.

With the --insert-ignore parameter, the data is inserted with the INSERT IGNORE method. Using so, when trying to insert a tuple with an already existent  primary key it is simply ignored. The duplicated key tuple is discarded and the previous existent tuple remains.

Another useful command may be the  INSERT ... ON DUPLICATE KEY UPDATE, that also, as the name suggests, deals with duplicated keys. This clause updates the value of the previous existing key in case of conflicts, so it can insert the new tuple.

For example if "myvalue" is a primary key, you can set:

INSERT INTO mytable (myvalue) VALUES (10)
ON DUPLICATE KEY UPDATE myvalue=myvalue+1;