Delete record with duplicate keyword segments in mysql table _ MySQL

Delete record bitsCN.com of some key fields in the mysql table

Clean up the duplicate data of Statistic every day [that is, the Date Server Item SubItem is identical, the Id must be different, and the Value may be the same]

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. * from Statistic a, Statistic B where. date = B. date and. server = B. server and. key = B. key and. subKey = B. subKey and. 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: (deduplicated)

BitsCN.com