从表中删除没有pk或id或mysql中唯一列的重复记录 [英] Delete duplicated records from a table without pk or id or unique columns in mysql

问题描述

我需要从一个表中删除所有重复的记录，问题是没有id或unique或key列，所以我无法进行以下操作:

I need to delete all the duplicated records from one of my tables the problem is that there isn't any id or unique or key column so I can't make something like this:

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

因为它需要一个id列或唯一或关键列 而且我不能做

because it needs an id column or unique or key column and I can't make an

ALTER IGNORE TABLE 'mytable' ADD UNIQUE INDEX 

因为某些信息总是必须重复，而其他信息则不需要 而且我做不到:

because there is information that will be always necessary duplicated but others don't and I can't make this:

DELETE FROM 'table' WHERE 'field' IN (SELECT 'field' FROM 'table' GROUP BY 'field'HAVING (COUNT('field')>1))

因为它会删除所有重复的内容，并且永远不会留下一个 这是我桌子的一个例子

because it will delete all the duplicated and never will leave one this is an example of my table

+----------+----------------------+-------------+-------------+
| 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
+----------+----------------------+-------------+-------------+

这就是我要离开的地方

+----------+----------------------+--------------+-------------+
| 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条记录....

and I can't truncate or delete the original table because its used 24/7 and has 10000000 records....

请帮助我.

推荐答案

使用 在 SQL-Fiddle 中进行了测试.

Tested in SQL-Fiddle.

注意:在5.5版中(由于快速索引创建的实现存在错误)，只有在ALTER:

SET SESSION old_alter_table=1 ;

这篇关于从表中删除没有pk或id或mysql中唯一列的重复记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！