How does MySQL delete records with duplicate key fields in a table?

MySQL databaseHow to delete some keyRepeated Fields? In this article, we use an example to introduce this deletion method. Let's talk about this example first.

First, let's take a look at the Statistic table structure:

Processing sample:

Main objectives:

Delete A Date Server Item SubItem with identical IDs and values.

For example:

| Mx1.dns.com.cn | SEND_MAIL | TOTAL | 14522 | 229 delete]

| Mx1.dns.com.cn | SEND_MAIL | TOTAL | 14795 | 248 reserved]

Implementation process:

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

 
 

  
  
use Statistic;  
  
  
 
  
  
create table s_tmp as select * from Statistic where 1=2; 
 
 

Step 2: extract new data to the temporary table based on the Id (auto-increment)

 
 

  
  
insert into s_tmp select a.* from Statistic a,Statistic 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 date data of the original table based on 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 in the temporary table to Statistic

 
 

  
  
insert into Statistic select * from  s_tmp; 
 
 

Step 5: finally clear the temporary table

 
 

  
  
delete * from s_tmp; 
 
 

Implementation Result: After deduplication)

This section describes how to delete some key fields in a MySQL table.