如何在MySQL表中删除重复项

Question

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

由于我仍在忙于下载和安装MySQL，因此我在无知和紧急情况下提出此请求，希望有人可以在此期间提供帮助。

 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
 )

- ProfK

---

我觉得看起来没问题。他们是否在使用支持子查询的MySQL版本？ - Ignacio Vazquez-Abrams

为什么要使用 having count(*) >= 1？它什么时候不需要呢？ - RichardTheKiwi

Answer 1

有多种方法可以达到同样的目的。这是其中之一。它非常快速，因此您可以将其用于大型数据库。不要忘记索引。 诀窍在于：使电话号码唯一并使用"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;

如果数据表已经存在，那么您只需要运行一个带有以下insert ignore select的create table select语句。最后，您还需要运行一些表重命名语句。就这样。

这种方法比删除操作快得多。

- Tom Schaefer

---

感谢提供有关MySQL的两部分教育。现在我已经掌握了“create table”的用法和“ignore”的技巧。 - ProfK

请注意，您可以使用ALTER IGNORE TABLE ADD UNIQUE(phoneNo)，它会在不创建另一个表的情况下静默地丢弃表中的重复项。顺便提一下，这是由于重复链接而导致的长时间评论。 - Wrikken

Answer 2

您可以通过以下方式选择唯一的内容：

select distinct(phoneNo) from bkPhone

将它们放入另一个表中，删除旧表并将新表重命名为旧名称。

- karim79

---

鉴于场景的简单性，允许新表和删除表，这是最简单、最有效的解决方案。谢谢。 - ProfK

Answer 3

MySQL抱怨，因为这没有意义。您尝试使用min()列进行聚合，而您正在按照该列分组。

现在，如果您想要删除同一人的重复电话号码，则SQL应该是：

delete from bkPhone
 where phoneId not in
 (
         select min(phoneId)
         from bkPhone
         group by firstName,lastName /* i.e. grouping by person and NOT grouping by phoneId */
         having  count(*) >= 1
 )

- vartec

---

太棒了。又是一个可爱的“having”子句的用例 :-) 但我认为你应该写“>”，而不是“>=”。这可能会加速事情。 - Lukas Eder

我完全看不出这个 having 的意义。按照现在的写法，它应该总是为真，所以毫无意义。如果你将其更改为 >1，那么内部 select 中没有重复行的行将被省略，因此会被外部的 delete 删除。我相信这不是你想要的。 - MvG

@LukasEder：使用 > 将删除所有仅具有单个电话的条目。我认为您没有注意到条件中有 not。 - vartec

SQL Fiddle报错：“#1093 - 无法在FROM子句中指定目标表'bkPhone'进行更新”。 - Jaak Kütt

Answer 4

还包括Mysql：

http://mssql-to-postgresql.blogspot.com/2007/12/deleting-duplicates-in-postgresql-ms.html

- Michael Buen