How do I remove duplicate rows in MySQL using PHPM

问题:

I have a database called 'master_database' and a table called 'info'

In the 'info' table I have multiple records and I need the 'email' field to not contain any duplicates but currently it does. What SQL command can I run to remove these duplicates?

回答1:

You can know the rows that are repeated by using this:

SELECT email, COUNT(email) FROM info GROUP BY email HAVING COUNT(email) > 1 

回答2:

DELETE FROM master_database.info WHERE info.ID NOT IN (SELECT MAX(info.ID) FROM master_database.info GROUP BY info.email HAVING COUNT(info.email) > 1)

This assumes you have a unique ID in the table where the higher the number the later the record, if you have a last_edited timestamp it might be better to use the MAX of that.

PLEASE TEST FIRST! Run the following to test:

SELECT * FROM master_database.info WHERE info.ID NOT IN (SELECT MAX(info.ID) FROM master_database.info GROUP BY info.email HAVING COUNT(info.email) > 1)

These values will be deleted.