mysql删掉重复数据没有id,从表中删除没有pk或id或mysql中唯一列的重复记录

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

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

+----------+----------------------+-------------+-------------+

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 |

+----------+----------------------+--------------+-------------+

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

Please help me.

解决方案

Adding a unique index (with all the columns of the table) with

ALTER IGNORE TABLE table_name

ADD UNIQUE INDEX all_columns_uq

(phone, address, name, cellphone) ;

Tested in

Note: In version 5.5 (due to a bug in the implementation of fast index creation), the above will work only if you provide this setting before the ALTER:

SET SESSION old_alter_table=1 ;