MySQL data duplicate checking and deduplication implementation statements

There is a table user, and the fields are id, nick_name, password, email, and phone.

1. Single field (nick_name)

Find all records with duplicate records

select * from user where nick_name in (select nick_name from user group by nick_name having count(nick_name)>1);

Find the record with the largest ID in each record group with duplicate records

select * from user where id in (select max(id) from user group by nick_name having count(nick_name)>1);

Find out the redundant records, but not the record with the smallest id

select * from user where nick_name in (select nick_name from user group by nick_name having count(nick_name)>1) and id not in (select min(id) from user group by nick_name having count(nick_name)>1);

Delete redundant duplicate records and keep only the record with the smallest id

delete from user where nick_name in (select nick_name from (select nick_name from user group by nick_name having count(nick_name)>1) as tmp1) and id not in (select id from (select min(id) from user group by nick_name having count(nick_name)>1) as tmp2);

2. Multiple fields (nick_name, password)

Find all the records with duplicate records

select * from user where (nick_name,password) in (select nick_name,password from user group by nick_name,password where having count(nick_name)>1);

Find the record with the largest ID in each record group with duplicate records

select * from user where id in (select max(id) from user group by nick_name,password where having count(nick_name)>1);

Find the redundant records in each duplicate record group, but do not find the one with the smallest ID.

select * from user where (nick_name,password) in (select nick_name,password from user group by nick_name,password having count(nick_name)>1) and id not in (select min(id) from user group by nick_name,password having count(nick_name)>1);

Delete redundant duplicate records and keep only the record with the smallest id

delete from user where (nick_name,password) in (select nick_name,password from (select nick_name,password from user group by nick_name,password having count(nick_name)>1) as tmp1) and id not in (select id from (select min(id) id from user group by nick_name,password having count(nick_name)>1) as tmp2);

The above is the detailed content of the implementation statements for MySQL data duplication checking and deduplication. For more information about MySQL data duplication checking and deduplication, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in: