Delete Duplicates Leaving One of Two Identical Records Delete Duplicates Leaving One of Two Identical Records

One of my MySQL database tables contains four BLObs in each row. When it was first set up the table had no keys. However when I now try to add a Primary Key (PK) I find I cannot do so as one of my people ran the same add twice. As a result I now have nearly a thousand duplicate records in over 5000 total records. Having Googled and searched here for a bit there seems more than one way of removing duplicates but which is the best one to use here?

Thank you...
Without knowing the structure, generically, you can GROUP BY the fields that have been duplicated and store it in a separate table for check purposes, then substitute the old table with the new one.

So, imagine that I have a table my_table with the fields a and b duplicated. I can do:

CREATE TABLE my_table_new (id int primary key auto_increment, a blob, b blob);

SET @i := 0;

INSERT INTO my_table_new SELECT @i := @i + 1, a, b FROM my_table GROUP BY a, b;


The exact query will depend on the older structure, the fields you want to group by, the order in which you want to insert, etc. This example creates an integer primary key from 1 to number of non-duplicate records.