How to find and delete duplicate rows in MySQL database

This article introduces how to find and delete duplicate rows in the MySQL database. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

When you sort (group) the b fields, c of the same value is divided into different groups, so COUNT (DISTINCT c) cannot be used to calculate the size. Internal functions such as COUNT () only act on the same group, and there is nothing you can do about the rows of different groups. Similarly, if you sort the c field, the b of the same value will be divided into different groups, which will not achieve our goal anyway.

Several correct methods

Perhaps the easiest way is to look for duplicate rows for a field separately, and then UNION them together, like this:

Select b as value, count (*) as cnt,'b' as what_col from a_b_c group by b having count (*) > 1 union select c as value, count (*) as cnt,'c'as what_col from a_b_c group by c having count (*) > 1 +-+ | value | cnt | what_col | +-+ | 1 | 3 | b | | 2 | 3 | b | 3 | 3 | b | | 1 | 3 | c | | 2 | 3 | c | | 3 | 3 | c | +-+ |

The output what_col field is used to indicate which field is repeated. Another way is to use nested queries:

Select a, b, c from a_b_c where b in (select b from a_b_c group by b having count (*) > 1) or c in (select c from a_b_c group by c having count (*) > 1) +-+ | a | b | c | +-- + | 7 | 1 | 1 | 8 | 1 | 2 | 9 | 1 | 3 | 10 | 2 | 11 | 2 | 2 | 12 | 2 | 3 | 13 | 3 | 1 | | 14 | 3 | 2 | | 15 | 3 | 3 | +-- +

This approach is much less efficient than using UNION and displays each repeated line instead of the repeated field value. There is also a way to join yourself with group's nested query results in table queries. The writing method is more complex, but it is necessary for complex data or situations with high requirements for efficiency.

Select a, a_b_c.b A_b_c.c from a_b_c left outer join (select b from a_b_c group by b having count (*) > 1) as b on a_b_c.b = b left outer join (select c from a_b_c group by c having count (*) > 1) as c on a_b_c.c = c.c where b.b is not null or c.c is not null So much for sharing about how to find and delete duplicate rows in MySQL database. I hope the above content can be of some help to you and learn more knowledge. If you think the article is good, you can share it for more people to see.