mysql 如何删除重复的行,如何从MySQL表中删除重复的行

I have a MySQL table like:

ID, Col1, Col2, Col3, Col4, etc...

ID is a primary key and has been working since the table's creation.

What I want to do is delete all but one records where all the other columns are identical.

解决方案DELETE DupRows.*

FROM MyTable AS DupRows

INNER JOIN (

SELECT MIN(ID) AS minId, col1, col2

FROM MyTable

GROUP BY col1, col2

HAVING COUNT(*) > 1

) AS SaveRows ON SaveRows.col1 = DupRows.col1 AND SaveRows.col2 = DupRows.col2

AND SaveRows.minId <> DupRows.ID;

Of course you have to extend col1, col2 in all three places to all columns.

Edit: I just pulled this out of a script I keep and re-tested, it executes in MySQL.