我需要从我的一个表中删除所有重复的记录,问题是没有任何 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
+----------+----------------------+-------------+-------------+
这就是我想要离开的
+----------+----------------------+--------------+-------------+
| 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 条记录....
请帮我。