从大型表中删除重复项

Question

我有一个非常大的表格，有1900万条记录，并且我遇到了重复行的问题。即使在这里也有很多类似的问题，但没有一个能给我一个令人满意的答案。需要考虑以下几点：

• 行的唯一性由两列location_id和datetime决定。
• 我希望执行时间尽可能快（<1小时）。
• 由于表格大小达到数GB，复制表格并不是很可行。
• 无需担心关系。

正如所说，每个location_id只能有一个不同的datetime，我想删除所有重复的实例。它们中的哪一个保留下来都无所谓，因为数据是相同的。

有什么好的想法吗？

- Tatu Ulmanen

---

考虑暂时删除索引和触发器（如果存在）。 - Pentium10

http://stackoverflow.com/questions/1585412/sql-to-delete-duplicate-records-in-a-table 这个方法有什么问题？ - Mike

@Pentium10，说得好，这可能加快速度，但如果我采用子查询解决方案，也可能损害性能。 - Tatu Ulmanen

尝试先生成删除SQL ID，而不发出删除命令，当您有索引时。在删除索引后，运行具有一堆ID的较长纯文本SQL。 - Pentium10

Answer 1

我认为你可以使用这个查询来从表格中删除重复的记录

ALTER IGNORE TABLE table_name ADD UNIQUE (location_id, datetime)

在执行此操作之前，先使用一些示例数据进行测试。然后再尝试以下步骤...

注意：在5.5版本中，它适用于MyISAM，但不适用于InnoDB。

- Vinodkumar SC

---

这看起来很有前途，我之前没有听说过这个功能。现在正在尝试它，我会告诉你结果如何。欢迎加入SO :) - Tatu Ulmanen

这个方法很有效，谢谢。用了31分钟处理了16982040行数据，其中有1589908个重复项。我简直不敢相信它可以这么简单，没有额外的表格或复杂的查询。 :) - Tatu Ulmanen

@Vinodkumar Saravana，我正在使用带有InnoDB的5.5版本，我看了你的说明，但为了确保，我还是尝试了一下。（当然，它没有成功），但您能否解释一下为什么它在InnoDB上不起作用？ - tixastronauta

@tixastronauta - 看起来MysQL的InnoDB版本存在一些bug。不过，你可以通过将表从InnoDB转换为MyIsam，然后应用alter ignore查询来解决问题。然后再将其转换回InnoDB。但在转换之前，请备份数据。或者你可以使用set session old_alter_table=1; 参考：http://dev.mysql.com/doc/refman/5.1/en/server-options.html - Vinodkumar SC

从mysql 5.7版本开始，“Alter Ignore table”不起作用了。也许它只是在InnoDB表上不起作用。您可以先将表转换为MyISAM，然后使用https://dev59.com/I2Mm5IYBdhLWcg3wKsgj#23421788删除重复项并将其备份到InnoDB中。 - Mohsen Abasi

Answer 2

SELECT *, COUNT(*) AS Count
FROM table
GROUP BY location_id, datetime
HAVING Count > 2

- Sjoerd

Answer 3

UPDATE table SET datetime  = null 
WHERE location_id IN (
SELECT location_id 
FROM table as tableBis
WHERE tableBis.location_id = table.location_id
AND table.datetime > tableBis.datetime)

SELECT * INTO tableCopyWithNoDuplicate FROM table WHERE datetime is not null

DROp TABLE table 

RENAME tableCopyWithNoDuplicate to table

所以你保留日期时间较低的那行。关于性能，我不确定，这取决于你的表列、服务器等等...

- remi bourgarel

Answer 4

您可以使用以下步骤删除重复项： 1- 将以下查询结果导出为txt文件：

select dup_col from table1 group by dup_col having count(dup_col) > 1

2- 将此添加到上述文本文件的开头并运行最终查询：

delete from table1 where dup_col in (.....)

请注意，'...'是在第一步创建的txt文件的内容。

- Mohsen Abasi

Answer 5

这个查询对于每种情况都完美地工作：已经在 MyIsam 引擎下测试了 200 万行。

ALTER IGNORE TABLE table_name ADD UNIQUE (location_id, datetime)

- Keith