How to delete duplicate records in some key fields of table in mysql

This article introduces how to delete duplicate records in some key fields of the table in mysql. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

Delete Date Server Item SubItem exactly the same, Id must be different, Value may be the same record

For example:

2011-07-27 | mx1.dns.com.cn | SEND_MAIL | TOTAL | 14522 | [delete]

2011-07-27 | mx1.dns.com.cn | SEND_MAIL | TOTAL | 14795 | [reserved]

Implementation process:

Step 1: create a temporary table with exactly the same structure as the Statistic table

Use Statistic

Create table s_tmp as select * from Statistic where 1: 2

Step 2: extract newer data to temporary tables according to Id (automatic growth)

Insert into s_tmp select a.* from Statistic a legendary b where a.Date=b.Date and a.Server=b.Server and a.Key=b.Key and a.SubKey=b.SubKey and a.id > b.id

Step 3: delete the data of the corresponding date of the original table according to the date information of the data in the temporary table

Delete from Statistic where Date in (select distinct Date from s_tmp)

Step 4: import the data from the temporary table into Statistic

Insert into Statistic select * from s_tmp

Step 5: finally empty the temporary table

Delete * from s_tmp

On how to delete some key fields of the table in mysql, repeat records are shared here. I hope the above content can be helpful to you and learn more knowledge. If you think the article is good, you can share it for more people to see.