选择具有重复项的记录

删除重复记录的最快和最简单的方法是发出一个非常简单的命令。

alter ignore table[tablename]添加唯一索引 UNIQUE_INDEX [字段名]

这将锁定表,如果这是一个问题,请尝试:

从表1 T1、表2 T2中删除T1
其中table1.duplicate_field=table2.duplicate_field(如果需要添加更多内容,例如and table.duplicate_field2=table2.duplicate_field2)
和Table1.Unique_Field>Table2.Unique_Field
然后分成几段跑得更快

要查找哪些姓氏重复:

  SELECT lastname, COUNT(lastname) AS rowcount 
    FROM table 
GROUP BY lastname 
  HAVING rowcount > 1

删除所有姓氏的一个副本。一直跑到什么都不做为止。不是很优雅。

DELETE FROM table 
 WHERE id IN (SELECT id 
                FROM (SELECT * FROM table) AS t 
            GROUP BY lastname 
              HAVING COUNT(lastname) > 1)

DUP How can I remove duplicate rows?

DELETE names
FROM names
LEFT OUTER JOIN (
   SELECT MIN(RowId) as RowId, lastname 
   FROM names
   GROUP BY lastname 
) as KeepRows ON
   names.lastname = KeepRows.lastname 
WHERE
   KeepRows.RowId IS NULL

假设:你有一个 RowId 柱

SELECT COUNT(*) as mycountvar FROM names GROUP BY lastname WHERE mycountvar > 1;

然后

DELETE FROM names WHERE lastname = '$mylastnamevar' LIMIT $mycountvar-1

但是:你为什么不把“姓”标记为“唯一”,这样就不可能出现重复的名字呢?