How to remove duplicate data from mysql database

Mysql database deduplication method: 1. Query the record that needs to be deleted, and one record will be retained; 2. Delete the duplicate record, and only one record will be retained. The code is [delete a from test1 a, (.. .) as bid from test1 c where..].

Mysql database method to remove duplicate data:

1. Query the record that needs to be deleted, and one record will be retained. .

select a.id,a.subject,a.RECEIVER from test1 a left join (select c.subject,c.RECEIVER ,max(c.id) as bid from test1 c where status=0 GROUP BY RECEIVER,SUBJECT having count(1) >1) b on a.id< b.bid where a.subject=b.subject and a.RECEIVER = b.RECEIVER and a.id < b.bid

2. Delete duplicate records and keep only one record. Note that subject,RECEIVER must be indexed, otherwise it will be very slow.

delete a from test1 a, (select c.subject,c.RECEIVER ,max(c.id) as bid from test1 c where status=0 GROUP BY RECEIVER,SUBJECT having count(1) >1) b where a.subject=b.subject and a.RECEIVER = b.RECEIVER and a.id < b.bid;

3. Find redundant duplicate records in the table. Duplicate records are judged based on a single field (peopleId).

select * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)

4. Delete redundant duplicate records in the table. Duplicate records are judged based on a single field. Field (peopleId) to judge, leaving only the record with the smallest rowid

delete from people where peopleId in (select peopleId from people group by peopleId  having count(peopleId) > 1) and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)

5. Delete the redundant duplicate records (multiple fields) in the table, leaving only the record with the smallest rowid

delete from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

It seems that if you want to be lazy and use a command to complete this thing, it doesn't seem to be obvious. Let's handle it step by step. The idea is to first create and copy a temporary table, then compare the data in the temporary table, and delete the data in the main table

alter table tableName add autoID int auto_increment not null; 
 
create table tmp select min(autoID) as autoID from tableName group by Name,Address; 
 
create table tmp2 select tableName.* from tableName,tmp where tableName.autoID = tmp.autoID; 
 
drop table tableName; 
 
rename table tmp2 to tableName;

More related free learning recommendations:mysql tutorial(Video)

The above is the detailed content of How to remove duplicate data from mysql database. For more information, please follow other related articles on the PHP Chinese website!