MYSQL从表中删除FIRST重复项

I have 511 duplicates in my table, I need to delete these, but not the originals. Therefore I want to delete every second occurrence of every duplicate.

我的表中有511个重复,我需要删除这些,但不是原件。因此,我想删除每个重复的每一次出现。

How can I do this?

我怎样才能做到这一点?

TABLE:

code  /   status  /   time
E3F4FF928A  /  0 /
07D95444BB  /  0 /  
07D95444BB /   0 / 
40006C128F  / 0  / 1315293012
2B45790E52   /  0  /
40006C128F  / 0  / 1315293012

3 个解决方案

#1

add a unique index to the table on the column that should be unique, and ignore errors

向列上的表添加唯一索引,该索引应该是唯一的,并忽略错误

alter ignore table X add unique index (column_a)

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

#2

Just run into this problem today (no unique key in the table).

今天遇到这个问题(表中没有唯一的密钥)。

I solved it like this

我这样解决了

DELETE FROM table where code=x and status=y and time=z LIMIT 1;

This will delete the first 1 row for the given criteria (if you have n duplicates, put n-1 to keep only one).

这将删除给定条件的前1行(如果您有n个重复项,则将n-1保留为仅保留一个)。

#3

DELETE t2 FROM table1 t2 
INNER JOIN table1 t1 ON 
      (t1.somefield = t2.somefield 
   AND t1.otherfield = t2.otherfield   /*add more fields if need be */
   AND t2.id > t1.id)
LEFT JOIN table1 t3 ON 
      (t1.somefield = t3.somefield 
   AND t1.otherfield = t3.otherfield   /*add more fields if need be */
   AND t3.id > t2.id) 
WHERE (t3.id IS NULL)  
ORDER BY t2.id ASC

This should only delete the 2nd duplicate and leave 3rd and beyond duplicates alone.

这应该只删除第二个副本,并留下第三个和超出重复项。

If you want something less esoteric, and you have a timestamp column, perhaps you want to do

如果你想要一些不太深奥的东西,并且你有一个时间戳列,也许你想要做

DELETE t2 FROM table1 t2 
INNER JOIN table1 t1 ON 
      (t1.somefield = t2.somefield 
   AND t1.otherfield = t2.otherfield   /*add more fields if need be */
   AND t2.`timestamp` > t1.`timestamp`)
WHERE (1=1)        /*In some mode(s) MySQL requires a where clause with delete*/
ORDER BY t2.id ASC

#1