从MySql表中删除重复的行 [英] Removing duplicate rows from a MySql table

问题描述

确实问过类似的问题，但我没有找到答案.

Similar questions were indeed asked, but I didn't find an answer.

我有一个带有3个非唯一字段的MySql表.我不要重复的行.意思是("a", "b", "c")和("a", "dasd", "dfsd")都可以(我不介意在第一字段中两次出现"a")，但是两次("a", "b", "c")都是错误的.

I have a MySql table with 3 non-unique fields. I don't want duplicate rows. Meaning ("a", "b", "c") and ("a", "dasd", "dfsd") are okay (I don't mind having "a" twice in the first fields), but having ("a", "b", "c") twice is wrong.

我需要一个查询，该查询将删除重复项，每个行组仅保留一行.

I need a query which will remove duplicates, leaving only one row for each row group.

推荐答案

编辑这已在SO 一种方法是在现有表的基础上创建一个新表.您可以通过以下方式完成此操作:

One approach would be to create a new table based on the existing table. You could do this through something like:

create table myNewTable SELECT distinct * FROM myOldTable;

然后，您可以清除旧表的数据，并在不想重复的字段上创建唯一约束:

Then you could clear the old table's data, and create a unique constraint on the fields you don't want duplicated:

TRUNCATE TABLE myOldTable;
ALTER TABLE myOldTable
    ADD UNIQUE (field1, field2);

然后将您的数据重新插入到原始表中.由于您是使用DISTINCT创建的myNewTable，因此您不应有任何重复项.

Then insert your data back into the original table. Because you created myNewTable using DISTINCT, you should not have any duplicates.

INSERT INTO myOldTable SELECT * FROM myNewTable;

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