Shulou(Shulou.com)06/01 Report--

This article mainly introduces the mysql database to remove duplicate data methods, with a certain reference value, friends can refer to the need. I hope you all have a lot of success after reading this article. Let's take a look at it together.

mysql database to remove duplicate data method: 1. Query the records to be deleted, will retain a record;2. Delete duplicate records, only retain a record, code is [delete a from test1 a, (...) as bid from test1 c where..】。

How to remove duplicate data from mysql database:

1. Query the records 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、删除重复记录，只保留一条记录。注意，subject,RECEIVER 要索引，否则会很慢的。 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、查找表中多余的重复记录，重复记录是根据单个字段（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 according to a single field (peopleId), leaving only records 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)

Delete redundant duplicate records (multiple fields) in the table, leaving only records 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 it is not obvious to be lazy to use a command to complete this matter. It is better to handle it step by step honestly. The idea is to create and copy a temporary table first, 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; Thank you for reading this article carefully. I hope that Xiaobian will share the method content of removing duplicate data in mysql database to help everyone. At the same time, I hope that everyone will support it more. Pay attention to the industry information channel. If you encounter problems, find them. Detailed solutions are waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.