This article mainly introduces how to delete duplicate MySQL data. it is of practical value to delete redundant data by creating an intermediate table, for more information about how to delete duplicate MySQL data, see the example in this article. Share it with you for your reference. The specific method is as follows:
Project Background
In a recent linux performance collection project, it was found that the thread program was slow to store in the database, and then carefully located and found a lot of redundant data in the database. During the collection, only one data point at the same time should be available for the same device. However, multiple data entries are stored in the database. I have no idea about how to cause this result. but to solve the problem of slow warehouse receiving, we need to delete redundant data first.
Problem Description
The table structure of the database is very simple, as follows:
The code is as follows:
+ ---------------- + -------------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ ---------------- + -------------- + ------ + ----- + --------- + ------- +
| Id | varcharar (255) | NO | PRI | NULL |
| Conf_id | varchar (255) | NO | MUL | NULL |
| Insert_time | datetime | YES | NULL |
| Cpu_usage | float (11, 2) | YES | NULL |
| Memory_usage | float (11, 2) | YES | NULL |
| Io_usage_write | float (11,2) | YES | NULL |
| Io_usage_read | float (11,2) | YES | NULL |
+ ---------------- + -------------- + ------ + ----- + --------- + ------- +
Query all data volumes
The code is as follows:
Select count (*) from perf_linux;
427366 output
Query the data volume of different devices at all time points
The code is as follows:
Select count (distinct conf_id, insert_time) from perf_linux;
42387 output
The data above shows that the data redundancy is about 10 times.
Let's look at the time group:
The code is as follows:
Select id, conf_id, insert_time from perf_linux order by insert_time, conf_id;
Output:
The code is as follows:
| 2a79f7cd-43a9-4c7b-adb2-450b6c04283e | 1 | 15:09:14 |
| 50d6f6c2-9c8b-45fd-98fd-2be211221cfd | 1 | 15:09:14 |
| 740b52e1-e868-4074-ba36-74e2634401b3 | 1 | 15:09:14 |
| 8b0096a4-9e85-492b-a131-e3505ca79a9c | 1 | 15:09:14 |
| 90a9e882-5220-4268-a56f-8d4ab4a7929b | 1 | 15:09:14 |
| D17403ed-24a4-45e8-b51b-2a95118383d9 | 1 | 15:09:14 |
| 0c2da917-579b-4080-857d-7159f38b44ac | 2 | 15:09:14 |
| 263083eb-8f63-4d2b-a03f-3320aa678735 | 2 | 15:09:14 |
| D6c57a38-080b-465a-a55a-beafd9daf32d | 2 | 15:09:14 |
| F672227b-1fb8-4b85-880d-2cc34b02880d | 2 | 15:09:14 |
| F80020fe-6cb5-48ec-beb0-4e8ebeb0ca57 | 2 | 15:09:14 |
| Ff633a35-824d-49ba-b78c-5bcc5df8d1cc | 2 | 15:09:14 |
| 5c41e48a-abfc-4268-a00e-ca7def7d5a5a | 3 | 15:09:14 |
| 60b7ab9e-c91a-4020-a6d3-7bceb1dc47c5 | 3 | 15:09:14 |
| 7b6cd2b8-ac6d-43eb-8858-e15885e676c8 | 3 | 15:09:14 |
| D53a3df5-08c4-4604-8fac-cb51077935f6 | 3 | 15:09:14 |
| D9e4ba14-f98d-42a8-b3bc-2879d58aa797 | 3 | 15:09:14 |
| F56f82f6-32a7-47f7-ae07-b13168743884 | 3 | 15:09:14 |
| 076c4c1b-0028-4a9c-a8c4-de655bd6ab6b | 4 | 15:09:14 |
| 2a90ad9e-11a5-4707-95e8-78491da658ad | 4 | 15:09:14 |
| 3b17ad1d-e589-4b65-93a7-d61fc99b4071 | 4 | 15:09:14 |
| 6988d6cf-44ef-47f7-808d-09791caf2d90 | 4 | 15:09:14 |
| 8404d281-f9e5-4263-a47e-128c05386758 | 4 | 15:09:14 |
| E042e310-7ff2-4e4d-8c98-71e3e4d57828 | 4 | 15:09:14 |
+ -------------------------------------- + --------- + --------------------- +
It can be seen that the data of the same device at the same time point is redundant. now we need to remove the redundant data.
Solution
The idea is as follows: first, you should judge by conf_id and time point to query groups (group by). you can obtain one in each group. Grouping is simple, but how can we get one group? I used an intermediate table.
Create an intermediate table and import the data to the intermediate table
The code is as follows:
Create table perf_linux_t like perf_linux;
Insert into perf_linux_t select * from perf_linux;
Add a field to the intermediate table. This field is self-incrementing.
The code is as follows:
Alter table 'Perf _ linux_t'
Add column 'auto _ id' int not null AUTO_INCREMENT,
Drop primary key,
Add primary key ('auto _ id ');
Delete useless data
First query
The code is as follows:
Select min (auto_id) as auto_id from perf_linux_t group by insert_time;
Delete incorrect data
The code is as follows:
Delete from perf_linux_t where auto_id not in (select min (auto_id) as auto_id from perf_linux_t group by insert_time );
Slow, output error:
The code is as follows:
Create table tmp like perf_linux_t;
Instead of deleting non-conforming data, you can save the conforming data to this new table.
The code is as follows:
Insert into tmp select * from perf_linux_t where auto_id in (select min (auto_id) as auto_id from perf_linux_t group by insert_time, conf_id );
Delete useless columns in this table
The code is as follows:
Alter table 'tmp'
Drop column 'auto _ id ',
Drop primary key;
Export back data
Delete original data
The code is as follows:
Truncate table perf_linux;
Insert data
The code is as follows:
Insert into perf_linux select * from tmp;
Delete intermediate table
The code is as follows:
Drop table tmp;
Drop table perf_linux_t;
Summary
In this method, the data is changed to 42387 records, and redundant data is deleted. But in fact, the program problem is not completely located, and it still needs to be observed to locate the problem.
I hope this article will help you design mysql database programs.