Mysql deduplication method

This article mainly introduces the related information of MySQL deduplication method. Friends who need it can refer to

MySQL deduplication method

[Elementary] There are very few duplicate lines

Use distinct to find them, and then manually delete them one by one.

[Intermediate] Deduplication according to the repetition of a single field

For example: Deduplication of the id field

Usage: Get the id For the values ​​of duplicate fields, use the rows where the same id field is located to compare the fields with different data, and delete all duplicate rows except the row where the smallest (or largest) field is located. Generally, the primary key is used for comparison, because the value of the primary key must be a unique value and must not be the same.

id  name

1    a

1    b

2    c

2    a

3    c

Result:

id  name

1    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:

The above bold and green words must be aliased and must use the format select * from (...), otherwise an error will be reported:

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

[Advanced] Repeat by multiple fields

For example: the same id and name Deduplication, that is: rows with the same ID and name are counted as duplicate rows, rows with the same ID but different names are counted as non-duplicate rows

Usage method: similar to a single field, generally use the primary key To compare, because the value of the primary key must be a unique value.

id  name  rowid

1  a      1

1  a      2

1  b      3

2  b      4

2  b      5

3  c      6

3  d     7

Result:

id  name  rowid

1  a      1 

1  b      3

2  b      4

3  c      6

3  d     7

Operation:

First type:

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 type :

Connect the values ​​of the id and name fields and insert them into the temporary table b_tmp, so that you can use the [Intermediate] single field judgment deletion method.

#Insert the value of the connection between the two fields and the unique value field in the a_tmp table into the b_tmp table

insert into b_tmp 

 select concat(id,name),rowid from a_tmp;

#查出需要留下来的行

select id_name,max(rowid)

 from b_tmp 

 group by id_name

 having count(id_name)>1;

#使用【中级】的方法，或存储过程完成去重的工作

[Ultimate] Each row has two copies of the same data

For example:

Instructions for use: The entire row of data is the same and cannot be deleted using SQL statements because there is no conditional restriction that can be used to leave one row and delete all rows that are identical to it. . 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 as the primary key, and it will automatically add the upper value.

id  name

1   a

1   a

1   b

1   b

2   c

2   c

3   c

3   c

Result:

id  name   rowid

1   a       1

1   b       3

2   c       5

3   c       7

Operation:

Add a self-increasing field and temporarily set it as the primary key.

Use the [Intermediate] and [Advanced] methods above.

The above is the detailed content of Mysql deduplication method. For more information, please follow other related articles on the PHP Chinese website!