按两列查找并删除重复行 [英] Find and remove duplicate rows by two columns

问题描述

我阅读了所有相关的重复问题/答案，我发现这是最相关的答案:

I read all the relevant duplicated questions/answers and I found this to be the most relevant answer:

INSERT IGNORE INTO temp(MAILING_ID,REPORT_ID) 
SELECT DISTINCT MAILING_ID,REPORT_IDFROM table_1
;

问题是我想删除 col1 和 col2 的重复项，但还想将 table_1 的所有其他字段包含到插入中.

The problem is that I want to remove duplicates by col1 and col2, but also want to include to the insert all the other fields of table_1.

我尝试以这种方式添加所有相关列:

I tried to add all the relevant columns this way:

INSERT IGNORE INTO temp(M_ID,MAILING_ID,REPORT_ID,
MAILING_NAME,VISIBILITY,EXPORTED) SELECT DISTINCT  
M_ID,MAILING_ID,REPORT_ID,MAILING_NAME,VISIBILITY,
EXPORTED FROM table_1
;


M_ID(int,primary),MAILING_ID(int),REPORT_ID(int),
MAILING_NAME(varchar),VISIBILITY(varchar),EXPORTED(int)

但它会将所有行插入到 temp 中(包括重复行)

But it inserted all rows into temp (including duplicates)

推荐答案

删除多列重复行最好的方法是最简单的:

The best way to delete duplicate rows by multiple columns is the simplest one:

添加唯一索引:

ALTER IGNORE TABLE your_table ADD UNIQUE (field1,field2,field3);

上面的 IGNORE 确保只保留第一个找到的行，其余的被丢弃.

The IGNORE above makes sure that only the first found row is kept, the rest discarded.

(如果您需要将来重复和/或知道它们不会再次发生，则可以删除该索引).

(You can then drop that index if you need future duplicates and/or know they won't happen again).

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