删除 MySQL 表中的重复条目 [英] Remove duplicate entries in a MySQL table

问题描述

我有一张有几千行的表.该表包含两列，name 和 email.我有几个重复的行，例如:

I have a table with a few thousand rows. The table contains two columns, name and email. I have several duplicate rows, for example:

• 约翰·史密斯 |[e-mail]
• 约翰·史密斯 |[e-mail]
• 艾丽卡·史密斯 |[e-mail]
• 艾丽卡·史密斯 |[e-mail]

删除所有重复结果的最简单方法是什么.例如，表格的内容将 = SELECT name, DISTINCT(email) FROM table.

What would be the easiest way to delete all duplicate results. For example, such that the table's content would = SELECT name, DISTINCT(email) FROM table.

推荐答案

您可以很容易地做到这一点，只需将该查询选择到另一个表中，然后重命名它以替换原来的.

You could pretty easily do this by selecting that query into another table, then renaming it to replace the original.

CREATE TABLE `table2` (
  `name` varchar(255), 
  `email` varchar(255), 
  UNIQUE KEY `email` (`email`));
INSERT INTO `table2` SELECT `name`, DISTINCT(`email`) FROM `table`;
RENAME TABLE `table` TO `table1`;
RENAME TABLE `table2` TO `table`;

请注意，此 CREATE 应根据您的实际表格格式进行调整.我在电子邮件字段中添加了唯一键，作为关于如何防止重复的建议.

Note that this CREATE should be adjusted to your actual table format. I added the unique key on the email field as a suggestion on how you would prevent duplicates in the first place.

或者，你可以循环这个

DELETE FROM `table` 
WHERE `email` IN (
  SELECT `email` FROM `table` GROUP BY `email` HAVING count(*) > 1
) LIMIT 1

这会在每次通话中删除一条重复记录.限制的重要性是不要删除任何重复的两行

Which would delete one duplicate record per call. The importance of the limit is to not remove both rows for any duplicate

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