logo标签列表

在mysql中删除没有主键、id或唯一列的表中的重复记录

mysqlsql-deleterecords
3

我需要从我的其中一个表格中删除所有重复的记录，但问题是没有任何id、唯一或键列，因此我无法像这样做：

delete from tbl using tbl,tbl t2 where tbl.locationID=t2.locationID
  and tbl.linkID=t2.linkID  and tbl.ID>t2.ID

因为需要一个id列或唯一键列，所以我无法创建

。
ALTER IGNORE TABLE 'mytable' ADD UNIQUE INDEX

因为有些信息需要始终重复使用，而其他信息则不需要。但我无法做到这一点：
DELETE FROM 'table' WHERE 'field' IN (SELECT 'field' FROM 'table' GROUP BY 'field'HAVING (COUNT('field')>1))

因为它将删除所有重复的，并且永远不会留下一个。 这是我的表格示例。


+----------+----------------------+-------------+-------------+
| phone    | address              | name        | cellphone   |
+----------+----------------------+-------------+-------------+
| 2555555  | 1020 PANORAMA        | JUAN CARLOS | 0999999999  | diferent address
| 2555555  | GABRIEL JOSE 1020    | JUAN CARLOS | 0999999999  | good one
| 2555555  | GABRIEL JOSE 1020    | JUAN CARLOS | 0999999999  | duplicated
| 2555555  | C ATARAZANA 1020     | SILVIA      | 0777777777  | another good one
| 2555555  | C ATARAZANA 1020     | SILVIA      | 0777777777  | another duplicated
| 2555555  | GABRIEL JOSE 1020    | VIOLETA     | 0888888888  | diferent person
+----------+----------------------+-------------+-------------+

and this is what I want to leave


+----------+----------------------+--------------+-------------+
| phone    | address              | name         | cellphone   |
+----------+----------------------+--------------+-------------+
| 2555555  | 1020 PANORAMA        | JUAN CARLOS  | 0999999999  |
| 2555555  | GABRIEL JOSE 1020    | JUAN CARLOS  | 0999999999  |
| 2555555  | C ATARAZANA 1020     | SILVIA       | 0777777777  |
| 2555555  | GABRIEL JOSE 1020    | VIOLETA      | 0888888888  |
+----------+----------------------+--------------+-------------+

我不能截断或删除原始表，因为它在24/7使用并且有10000000条记录...

请帮帮我。

可能是删除重复行，只保留最早的行？的重复问题。 - Ashesh Kumar
不行，它不能与之一起使用...那个例子适用于具有类似id列、键列或唯一列的表，我的表没有这些列，也无法更改。 - ss4sgoku
4个回答
7

使用ALTER IGNORE添加一个包含表中所有列的唯一索引，可以去除重复项：

ALTER IGNORE TABLE table_name
  ADD UNIQUE INDEX all_columns_uq
    (phone, address, name, cellphone) ;

在 SQL-Fiddle 上测试通过。

注意：在版本 5.5 中（由于快速索引创建实现中的错误），上述代码仅在您在 ALTER 命令之前设置此选项时有效：

SET SESSION old_alter_table=1 ;
无法做到这一点...因为总会存在一些包含重复信息的列，例如在同一个电话号码下可能有3个人...所以我无法使电话号码唯一，地址也无法唯一，因为如果是建筑物，可能会住着许多不同电话号码的家庭...所以它不能被使用... :( - ss4sgoku
+1 我也是。我尝试了你的解决方案，但好像没能让它工作。不过我喜欢这种方法。尝试 - John Ruddell
@JohnRuddell 这段代码在 SQLFiddle 版本 5.1 中是可以工作的，但在版本 5.5 中却无法运行。可能与某些 MySQL 设置有关。IGNORE 在版本 5.6 中已经被弃用，并且在版本 5.7 中已经被删除。 - ypercubeᵀᴹ
不，如果你移除 IGNORE，重复的数据肯定会产生错误。请参考我回答中的链接 sqlfiddle，它使用了 IGNORE 并且可以正常工作。 - ypercubeᵀᴹ
是的，我从未使用过 ignore，但我认为那就是它的作用... 我喜欢这种方法，因为它非常简单。而且它并不是添加一个主键，只是一个索引。在我看来，这应该是被接受的答案！ - John Ruddell
1

很简单，只需创建一个临时表并删除另一个表，然后重新创建它即可。

CREATE TEMPORARY TABLE IF NOT EXISTS no_dupes AS 
(SELECT * FROM test GROUP BY phone, address, name, cellphone);

TRUNCATE table test;
INSERT INTO test (phone, address, name, cellphone) 
SELECT phone, address, name, cell FROM no_dupes;

工作演示

如果使用“select distinct”语句，那么可以实现这个功能，但我不能删除原始表格……这是一个好的选择，但不适用于我的情况…. :( - ss4sgoku
@ss4sgoku，请检查我的编辑...尝试使用truncate，它将完全清空表格但不会删除它。 - John Ruddell
是的，那确实是一个不错的方法，但我不能截断或删除原始数据...而且它有10000000行，因此重新制作需要一些时间和处理能力...这就是为什么我仍在寻找其他方法的原因... - ss4sgoku
@ss4sgoku，truncate只是清空表格...如果你想的话也可以删除所有行...你不能这样做吗？我以为你有删除权限...逻辑是将要保留的所有内容保存在临时表中，然后从主表中删除所有内容，然后将来自临时表的内容插入到主表中...我不知道为什么你不能这样做。 - John Ruddell
@ypercube 谢谢，我假设每个住在不同房子里的人都有不同的地址名称和手机号码，因为这些通常是唯一的，但我还是加上了以确保安全 :) - John Ruddell
显示剩余3条评论
0
我会使用子查询。类似这样：
DELETE FROM table1
WHERE EXISTS (
SELECT field1 
FROM table1 AS subTable1 
WHERE table1.field1 = subTable1.field1 and table1.field2 = subTable1.field2)

虽然我还没有尝试过这个。

啊，我的错。如果我们将子查询的结果放入某个临时表中（在使用distinct之后），删除所有重复记录，然后将临时表中的记录添加到原始表中。虽然这可能不是很直接，但这是我能想到的唯一方法。很想看到另一个答案 :) - Brain Balaka
这是一个好主意...我从未尝试过这样的方法，但它可能会奏效。我将创建一个临时表，在其中删除原始表中的所有重复行，并从临时表插入数据。这是一个很好的解决方案。 - ss4sgoku
-2

每个表格总是有一个主键，但你可以将多列组合成唯一标识符，因此如果需要的话，可以使用整行作为唯一标识符...但我不建议使用整行，你应该搜索哪些最重要的列可以用作主键，当你完成后，可以复制数据，如果没有问题，MySQL 就不会复制重复的行。

对我的糟糕英语感到抱歉。

必须始终有一个主键...但这个没有，我无法更改它...所以我没有正常的方法来删除重复的行...这就是为什么我头疼的原因...我可以找到所有重复的记录，有些记录重复了900次，有些重复了400次，还有一些只重复了3或2次，但我需要只留下一个。 - ss4sgoku
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接