如何从 mysql 数据库中删除重复的条目?

2022-01-10 00:00:00 duplicate-removal mysql

我有一张带有一些 ids + 标题的表格.我想让标题列独一无二，但它已经有超过 60 万条记录，其中一些是重复的(有时是几十倍).

I have a table with some ids + titles. I want to make the title column unique, but it has over 600k records already, some of which are duplicates (sometimes several dozen times over).

如何删除除一个之外的所有重复项，以便在之后向标题列添加唯一键?

How do I remove all duplicates, except one, so I can add a UNIQUE key to the title column after?

推荐答案

此命令添加一个唯一键，并删除所有生成错误的行(由于唯一键).这会删除重复项.

This command adds a unique key, and drops all rows that generate errors (due to the unique key). This removes duplicates.

ALTER IGNORE TABLE table ADD UNIQUE KEY idx1(title);

<小时>
请注意，此命令可能不适用于 InnoDB 表MySQL 的版本.请参阅这篇文章了解解决方法.(感谢匿名用户"提供此信息.)

Note that this command may not work for InnoDB tables for some versions of MySQL. See this post for a workaround. (Thanks to "an anonymous user" for this information.)

相关文章