问题描述
我需要从一个表中删除所有重复的记录,问题是没有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 columnand 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'tand 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 onethis 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....
请帮助我.
推荐答案
使用 ALTER IGNORE
将删除重复项:
ALTER IGNORE TABLE table_name
ADD UNIQUE INDEX all_columns_uq
(phone, address, name, cellphone) ;
在 SQL-Fiddle 中进行了测试.
Tested in SQL-Fiddle.
注意:在5.5版中(由于快速索引创建的实现存在错误),只有在ALTER
:
SET SESSION old_alter_table=1 ;
这篇关于从表中删除没有pk或id或mysql中唯一列的重复记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!