Summary of methods for finding and deleting duplicate data in MySQL tables

Sometimes we save a lot of duplicate data in the database table. These duplicate data waste resources. We want to delete them. How should we deal with it? Let’s take a look below.

Let's take a look at our table data first. Some data is repeated.

To find duplicate data, we can use the having statement in MySQL, as shown in the figure.

After executing this statement, we can see that the current result shows the fields with duplicate data in the table.

To delete these duplicate data, we find out the IDs of these data, add the id field in the select statement, and use the max function to get the last id of the duplicate data.

The execution result is shown in the figure, and the ids of the duplicate data are 8 and 9.

In this way, we can use the delete statement to delete the data of these two ids.

But what if there is a lot of duplicate data and you don’t want to write these IDs one by one?

Add a subquery to query only the id field, as shown in the figure.

Then just add a delete statement outside, the detailed code is shown in the figure.

It should be noted that if there are three or more rows of duplicate data, we need to execute this statement multiple times, because executing it once will only delete one row in each set of duplicate data.