mysql筛选删除重复值,从MySQL表中删除重复值的最佳方法是什么？

I have the following SQL to delete duplicate values form a table,

DELETE p1

FROM `ProgramsList` p1, `ProgramsList` p2

WHERE p1.CustId = p2.CustId

AND p1.CustId = 1

AND p1.`Id`>p2.`Id`

AND p1.`ProgramName` = p2.`ProgramName`;

Id is auto incremental

for a given CustId ProgramName must be unique (currently it is not)

The above SQL takes about 4 to 5 hours to complete with about 1,000,000 records

Could anyone suggest a quicker way of deleting duplicates from a table?

解决方案

First, You might try adding indexes to ProgramName and CustID fields if you don't already have them.

De-Duping

You can group your records to identify dupes, and as you are doing that, grab the min ID value for each group. Then, just delete all records whose ID is not one of the MinID's.

In-Clause Method

delete from

ProgramsList

where

id not in

(select min(id) as MinID

from ProgramsList

group by ProgramName, CustID)

Join-Method

You may have to run this more than once, if there are many members per group.

DELETE P

FROM ProgramsList as P

INNER JOIN

(select count(*) as Count, max(id) as MaxID

from ProgramsList

group by ProgramName, CustID) as A on A.MaxID = P.id

WHERE A.Count >= 2

Some people have performance issues with the In-Clause, some don't. It depends a lot on your indexes and such. If one is too slow, try the other.