如何在不使用临时表的情况下删除MySQL表中的所有重复记录

Question

我看过很多类似的内容，但没有一个完全符合我想要的。

我有一张名为TableA的表，其中包含用户对可配置问卷的答案。列是member_id, quiz_num, question_num, answer_num。

某些成员的答案被提交了两次。所以我需要删除重复记录，但确保留下一行。

由于没有主列，因此可能会出现两行或三行具有相同数据的情况。

是否有查询可以删除所有重复项？

- MivaScott

---

你反对创建一个临时表并在删除重复记录后使用简单的drop table语句将其删除，这是有原因的吗？我可以提供SQL来创建仅包含唯一记录的临时表，从原始表中删除记录，加载唯一数据，然后删除临时表。如果不是大型数据库，这应该不会花费太长时间。这里有一篇关于此过程的好文章：http://www.databasejournal.com/features/mysql/article.php/10897_2201621_2/Deleting-Duplicate-Rows-in-a-MySQL-Database.htm - Kyle

可能是如何在MySQL数据库中删除重复记录？的重复问题。 - Andy Lester

我正在寻找一种解决方案，可以轻松（在最广泛的意义上）实时重复。使用额外的表格，无论是临时的还是不临时的，都意味着每次检测到这种情况都会使网站崩溃。最好的解决方案是确保它从一开始就不会发生，但在那之前，我希望定期进行此检查/修复，以确保报告不会给出错误的结果。 - MivaScott

正如我在答案中提到的，创建一个表索引。它将删除重复数据，并且不允许您在未来添加重复数据。 - Saharsh Shah

可能是Remove duplicate rows in MySQL的重复问题。 - simhumileco

Answer 1

在你的表上添加唯一索引:

ALTER IGNORE TABLE `TableA`   
ADD UNIQUE INDEX (`member_id`, `quiz_num`, `question_num`, `answer_num`);

另一种方法是：

在您的表中添加主键，然后您可以使用以下查询轻松地从表中删除重复项：

DELETE FROM member  
WHERE id IN (SELECT * 
             FROM (SELECT id FROM member 
                   GROUP BY member_id, quiz_num, question_num, answer_num HAVING (COUNT(*) > 1)
                  ) AS A
            );

- Saharsh Shah

---

注意：如果您的表格包含3个或更多重复的行，则需要运行此操作多次。 - zgr024

如果解释一下添加带有IGNORE的唯一索引的后果，那么这个答案会更有帮助：显然它会删除重复的行？我们知道它是否保留第一个重复项吗？ - LarsH

看起来 alter ignore table 很快就会消失了：http://dev.mysql.com/worklog/task/?id=7395 - juacala

我认为删除命令将会删除所有重复的数据。它不会包含任何一行重复的数据。 - Ankit Bajpai

@juacala 是正确的：从MySQL 5.7.4开始，ALTER TABLE命令中的IGNORE子句已被移除，使用它将会产生错误。 - Illya Moskvin

Answer 2

与其使用drop table TableA，您可以删除所有记录（delete from TableA;），然后使用来自TableA_Verify的记录重新填充原始表格（insert into TAbleA select * from TAbleA_Verify）。这样，您就不会失去对原始表格（索引等）的所有引用。

CREATE TABLE TableA_Verify AS SELECT DISTINCT * FROM TableA;

DELETE FROM TableA;

INSERT INTO TableA SELECT * FROM TAbleA_Verify;

DROP TABLE TableA_Verify;

- jveirasv

Answer 3

这里不使用临时表，而是使用实际表。如果问题只涉及到临时表而不是创建或删除表的问题，那么这将会起作用：

SELECT DISTINCT * INTO TableA_Verify FROM TableA;

DROP TABLE TableA;

RENAME TABLE TableA_Verify TO TableA;

- christoph

Answer 4

感谢jveirasv提供的上面的答案。

如果您需要删除特定列的重复项，可以使用以下方法（如果表中有时间戳等不同的情况）

CREATE TABLE TableA_Verify AS SELECT * FROM TableA WHERE 1 GROUP BY [COLUMN TO remove duplicates BY];

DELETE FROM TableA;

INSERT INTO TableA SELECT * FROM TAbleA_Verify;

DROP TABLE TableA_Verify;

- nikolais

Answer 5

在您的表上添加唯一索引：

ALTER IGNORE TABLE TableA   
ADD UNIQUE INDEX (member_id, quiz_num, question_num, answer_num);

它的工作非常出色。

- Dina Elwy

---

谢谢，它可以工作，但我收到了1个警告：1681 'IGNORE'已被弃用并将在未来的版本中删除。重复项：1 警告：1 - Lebnik

Answer 6

如果您没有使用任何主键，则可以在一次操作中执行以下查询，通过替换值：

# table_name - Your Table Name
# column_name_of_duplicates - Name of column where duplicate entries are found

create table table_name_temp like table_name;
insert into table_name_temp select distinct(column_name_of_duplicates),value,type from table_name group by column_name_of_duplicates;
delete from table_name;
insert into table_name select * from table_name_temp;
drop table table_name_temp

1. 创建临时表并存储不重复的值
2. 创建空原始表
3. 从临时表向原始表插入值
4. 删除临时表

在操作数据库之前，建议先备份数据库。

- Sandesh Mhatre

Answer 7

另一种方法是创建一个具有相同结构的新临时表。

CREATE TABLE temp_table AS SELECT * FROM original_table LIMIT 0

然后在表中创建主键。

ALTER TABLE temp_table ADD PRIMARY KEY (primary-key-field)

最后，将原始表中所有记录复制到新表中，同时忽略重复的记录。

INSERT IGNORE INTO temp_table AS SELECT * FROM original_table

现在您可以删除原始表并重命名新表。

DROP TABLE original_table
RENAME TABLE temp_table TO original_table

- user1838915

Answer 8

正如评论中所指出的那样，如果项目被重复多次，则必须多次运行Saharsh Shah答案中的查询。

这里提供了一种解决方案，不会删除任何数据，并在整个过程中保留原始表中的数据，允许删除重复项同时保持表“活动”：

alter table tableA add column duplicate tinyint(1) not null default '0';

update tableA set
duplicate=if(@member_id=member_id
             and @quiz_num=quiz_num
             and @question_num=question_num
             and @answer_num=answer_num,1,0),
member_id=(@member_id:=member_id),
quiz_num=(@quiz_num:=quiz_num),
question_num=(@question_num:=question_num),
answer_num=(@answer_num:=answer_num)
order by member_id, quiz_num, question_num, answer_num;

delete from tableA where duplicate=1;

alter table tableA drop column duplicate;

这基本上是检查当前行是否与上一行相同，如果是，则将其标记为重复项（order语句确保重复项会显示在一起）。然后删除重复记录。我在最后删除了duplicate列，使其恢复到原始状态。

看起来alter table ignore也可能很快就会消失：http://dev.mysql.com/worklog/task/?id=7395

- juacala

Answer 9

已在mysql 5中测试。不确定其他版本。 如果您希望保留具有最低id值的行：

DELETE n1 FROM 'yourTableName' n1, 'yourTableName' n2 WHERE n1.id > n2.id AND n1.member_id = n2.member_id and n1.answer_num =n2.answer_num

如果您想保留具有最高ID值的行：

DELETE n1 FROM 'yourTableName' n1, 'yourTableName' n2 WHERE n1.id < n2.id AND n1.member_id = n2.member_id and n1.answer_num =n2.answer_num

- TanvirChowdhury