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)