How to remove duplicate records with phpMyAdmin?

Monday, 3 March 2008

How to remove duplicate records with phpMyAdmin?

Sometimes you forgot to add an UNIQUE constraint to a field and you end up having
so many unneeded duplicated records.

My solution is basically the following.

1) Export the table's data
2) Empty the table
3) Set an UNIQUE constraint to a column
4) Reimport data

Here are the details:

This is the most important step, so please pay attention.

1) Exporting the table's data

In phpMyAdmin choose your table
Click -> Export
Click on the following checkboxes (marked in bold are extremely important).

[Structure]
[X] Add IF NOT EXISTS

[Data]
[X] Complete inserts
[X] Extended inserts
[X] Use ignore inserts

I your table is quite big I would suggest you to choose
Compression: (o) "gzipped"

Click the Go button

2) Empty the table
Make sure your export is successful or YOU WILL LOOSE YOUR DATA!!!

Click Empty table and confirm.

3) Set a UNIQUE constraint to a column

Go to Structure tab of your table
Choose the column and click on the U image to make the field UNIQUE

4) Reimport data

Go to Import

Click browse to locate the previously dumped file and then click GO button

Note:
If the file is too big you might have to import the data through ssh.

Upload the YOUR_DATABASE_DUMP.sql.gz

gunzip YOUR_DATABASE_DUMP.sql.gz
mysql --user=YOUR_DB_USER -p YOUR_DB_NAME < YOUR_DATABASE_DUMP.sql
You will be prompted for a password and after several minutes you're done.

Hope it helps.

Disclaimer: Do it on your own risk!


 Digg  Del.icio.us  Reddit  SlashDot

7 comments:

Keshav said...

-or- you can delete duplicates by using single SQL statement.
-ankur patwa

Svetoslav Marinov said...

of course :)

Anonymous said...

better to backup the table to tableOLD before emptying...

Svetoslav Marinov said...

absolutely!
backup must always be done before doing something *dangerous*

xuslik said...

it's dangerous, but it's great... worked very well with me. Thanks for the tip!

xuslik said...

it's dangerous, but it's great... worked very well with me. Thanks for the tip!

Svetoslav Marinov said...

I know xuslik.
Always make backups.