How to deduplicate MySQL and how to sort out MySQL

How to deduplicate MySQL and how to sort out MySQL

How to deduplicate MySQL

Primary: there are very few duplicate rows.

Use distinct to find out and delete one row manually.

[Intermediate] deduplication Based on the repetition of a single Field

For example, deduplicate the id field

How to Use: obtain the value of the duplicate field of the id. Compare the fields with different data in the row where the same id field is located, except for the minimum (or maximum) value) all repeated rows except the row where the field of is located. The primary key is usually used for comparison, because the value of the primary key must be unique and definitely not the same.

id  name1    a1    b2    c2    a3    c

Result:

id  name1    a 2    a

Operation:

delete from a_tmp where id in (select * from (select b.id from a_tmp b group by b.id having count(b.id) >1) bb) and name not in (select * from (select min(a.name) from a_tmp a GROUP BY a.id having count(a.id) >1) aa);

Note:

For the bold and green words above, aliases must be added and select * from (…) must be used (......) Otherwise, an error is returned:

[Err] 1093-You can't specify target table 'A _ tmp 'for update in FROM clause

[Advanced] Repeat Multiple Fields

For example, deduplicate rows with the same id and name, that is, duplicate rows with the same id and name, and duplicate rows with the same id and name

Usage: similar to a single field, the primary key is generally used for comparison, because the primary key value must be a unique value.

id  name  rowid1  a      11  a      21  b      32  b      42  b      53  c      63  d     7

Result:

id  name  rowid1  a      1 1  b      32  b      43  c      63  d     7

Operation:

First:

delete from a_tmp where (id,name) in (select * from (select b.id,b.name from a_tmp b group by b.id,b.name having count(b.id) >1) bb) and rowid not in (select * from (select min(a.rowid) from a_tmp a group by a.id,a.name having count(a.id) >1) aa);

Second:

Connect the values of the id and name fields and insert them into the B _tmp temporary table. This allows you to use the [intermediate] Single-field judgment and deletion method.

# Insert the values of the two fields that are unique in the_tmp table into the B _tmp table.

Insert into B _tmp select concat (id, name), rowid from a_tmp; # Find the row to be left: select id_name, max (rowid) from B _tmp group by id_name having count (id_name)> 1; # remove duplicates using the [intermediate] method or stored procedure

[Ultimate] each row has two identical data copies.

For example:

Usage: the data in the entire row is the same and cannot be deleted using an SQL statement, because there are no conditions to delete a row from the same row. If there are no different fields, you can create different fields by yourself. That is, add a field, set it to auto-increment, and set it to the primary key. It will automatically add the upper value.

id  name1   a1   a1   b1   b2   c2   c3   c3   c

Result:

id  name   rowid1   a       11   b       32   c       53   c       7

Operation:

Add an auto-increment field and temporarily set it as the primary key.

Use the above [intermediate] and [advanced] methods.

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!