Common Methods for deleting duplicate records in MySQL

MySQL deduplication is a common operation. The following describes several common methods for deleting duplicate records in MySQL. If you have encountered any problems in deleting duplicate records in MySQL, take a look.

When using mysql, you sometimes need to query records with unique fields. Although mysql provides the keyword distinct to filter out redundant duplicate records, only one record is retained, but it is often used to return the number of records that do not repeat, instead of returning all values of records that do not repeat. The reason is that distinct can only return its target field, but cannot return other fields. If distinct cannot solve the problem, I only use double loop query to solve the problem, this will undoubtedly directly affect the efficiency of a station with a large data volume.
Let's take a look at the example below:

The table structure is as follows:
Id name
1
2 B
3 c
4 c
5 B

The structure of the basic table is like this. This is just a simple example. The actual multi-Table query is much more complicated.

For example, if you want to use a statement to query all data with no duplicate names, you must use distinct to remove redundant duplicate records.

Select distinct name from table
The result is:

Name
A
B
C

It seems that the effect has been achieved, but what I want to get is the id value? Modify the query statement:

Select distinct name, id from table

The result is:

Id name
1
2 B
3 c
4 c
5 B

How does distinct not work? The role is actually effective, but it also serves two fields, that is, it must have the same id and name to be excluded.

Modify the query statement again:

Select id, distinct name from table

Unfortunately, you cannot get anything except the error message. You must start with distinct. Is it difficult to place distinct in the where condition? Try again and report an error.

I tried other methods that I could think of for a long time. I finally found a usage in the mysql manual. I was excited to use group_concat (distinct name) and group by name to implement the functions I needed, you can try it now.

Error reported, depressing!

I can't even go through the mysql manual. I gave me hope first, and then pushed me down.

Check again. The group_concat function is supported by 4.1, dizzy. I have 4.0. No way. Upgrade. The upgrade is successful.

Finally, the customer must be asked to upgrade.

Suddenly, the ghost machine flashed. Since the group_concat function can be used, can other functions be used?

Hurry and use the count function for a try. It's as easy as it takes so much time.

Now release the complete statement:

Select *, count (distinct name) from table group by name

Result:

Id name count (distinct name)
1 a 1
2 B 1
3 c 1

The last item is redundant, so you don't have to worry about it.

It turned out that mysql was so stupid that I would just lie to him. Now I hope you will not be confused by this problem.

By the way, group by must be placed before order by and limit, otherwise an error will be reported.

 

Restoration of MySQL MyISAM Table Structure

In-depth parsing MySQL Association Table Creation

Alternative usage of MySQL table alias

MySQL left join query Experience Summary

How to display MYSQL table information