如何删除MySQL表中的重复项 [英] How to delete Duplicates in MySQL table

问题描述

我向客户提供了以下查询以删除重复的电话号码.MSSQL 数据库中的记录，但现在他们也需要在 MySQL 上执行此操作，并且他们报告 MySQL 抱怨查询的格式.我已经为我的代码示例包含了一个带有重复项的测试表的设置，但实际的删除查询才是最重要的.

I've given a client the following query to delete duplicate phone no. records in an MSSQL database, but now they need to also do it on MySQL, and they report that MySQL complains about the format of the query. I've included the setup of a test table with duplicates for my code sample, but the actual delete query is what counts.

我是在无知和紧急的情况下问这个问题的，因为我还在忙于下载和安装 MySQL，在此期间也许有人可以提供帮助.

I'm asking this in ignorance and urgency, as I am still busy downloading and installing MySQL, and just maybe somebody can help in the mean time.

 create table bkPhone
 (
     phoneNo nvarchar(20),
     firstName nvarchar(20),
     lastName nvarchar(20)
 )
 GO

 insert bkPhone values('0783313780','Brady','Kelly')
 insert bkPhone values('0845319792','Mark','Smith')
 insert bkPhone values('0834976958','Bill','Jones')
 insert bkPhone values('0845319792','Mark','Smith')
 insert bkPhone values('0828329792','Mickey','Mouse')
 insert bkPhone values('0834976958','Bill','Jones')

 alter table bkPhone add phoneId int identity

 delete from bkPhone
 where phoneId not in
 (
     select min(phoneId)
     from bkPhone
     group by phoneNo,firstName,lastName
     having  count(*) >= 1
 )

推荐答案

通向罗马的方法很多.这是一.它非常快.所以你可以将它与大型数据库一起使用.不要忘记 indeces.诀窍是:使 phoneNo 唯一并使用ignore".

Many ways lead to Rome. This is one. It is very fast. So you can use it with big databases. Don't forget the indeces. The trick is: make phoneNo unique and use "ignore".

drop table if exists bkPhone_template;
create table bkPhone_template (
         phoneNo varchar(20),
         firstName varchar(20),
         lastName varchar(20)
 );

insert into bkPhone_template values('0783313780','Brady','Kelly');
 insert into bkPhone_template values('0845319792','Mark','Smith');
 insert into bkPhone_template values('0834976958','Bill','Jones');
 insert into bkPhone_template values('0845319792','Mark','Smith');
 insert into bkPhone_template values('0828329792','Mickey','Mouse');
 insert into bkPhone_template values('0834976958','Bill','Jones');

drop table if exists bkPhone;
create table bkPhone like bkPhone_template;
alter table bkPhone add unique (phoneNo);

insert  ignore into bkPhone (phoneNo,firstName,lastName) select phoneNo,firstName,lastName from bkPhone_template;

drop table bkPhone_template;

如果数据表已经存在，那么您只需要使用以下插入忽略选择运行创建表选择.最后，您必须运行一些表重命名语句.仅此而已.

If the data table already exists, then you only have to run a create table select with a following insert ignore select. At the end you have to run some table renaming statements. That's all.

此解决方法比删除操作快得多.

This workaround is much,much faster then a delete operation.

这篇关于如何删除MySQL表中的重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！