试着使用这个查询:

SELECT name, COUNT(*) value_count FROM company_master GROUP BY name HAVING value_count > 1;

进一步采取@maxyfc的答案，我需要找到所有返回的重复值的行，这样我就可以在MySQL Workbench中编辑它们:

SELECT * FROM table
   WHERE field IN (
     SELECT field FROM table GROUP BY field HAVING count(*) > 1
   ) ORDER BY field

SELECT ColumnA, COUNT( * )
FROM Table
GROUP BY ColumnA
HAVING COUNT( * ) > 1

SELECT  *
FROM    mytable mto
WHERE   EXISTS
        (
        SELECT  1
        FROM    mytable mti
        WHERE   mti.varchar_column = mto.varchar_column
        LIMIT 1, 1
        )
ORDER BY varchar_column

这个查询返回完整的记录，而不仅仅是不同的varchar_column。

这个查询不使用COUNT(*)。如果有很多重复项，COUNT(*)是昂贵的，并且不需要整个COUNT(*)，只需要知道是否有两行具有相同的值。

这是通过相关查询底部的LIMIT 1,1来实现的(本质上意味着“返回第二行”)。EXISTS只在前面提到的第二行存在时才返回true(即至少有两行具有相同的varchar_column值)。

当然，在varchar_column上建立索引将大大加快此查询的速度。

如果要删除具有多个字段的重复行，首先将它们取消为唯一不同的行指定的新唯一键，然后使用group by命令删除具有相同新唯一键的重复行:

Create TEMPORARY table tmp select concat(f1,f2) as cfs,t1.* from mytable as t1;
Create index x_tmp_cfs on tmp(cfs);
Create table unduptable select f1,f2,... from tmp group by cfs;

一个非常晚的贡献…万一这能帮助到以后的任何人…我有一个任务是在一个银行应用程序中找到匹配的交易对(实际上是账户到账户转账的双方)，以识别每个账户间转账交易的“从”和“到”，所以我们最终得到了这个:

SELECT 
    LEAST(primaryid, secondaryid) AS transactionid1,
    GREATEST(primaryid, secondaryid) AS transactionid2
FROM (
    SELECT table1.transactionid AS primaryid, 
        table2.transactionid AS secondaryid
    FROM financial_transactions table1
    INNER JOIN financial_transactions table2 
    ON table1.accountid = table2.accountid
    AND table1.transactionid <> table2.transactionid 
    AND table1.transactiondate = table2.transactiondate
    AND table1.sourceref = table2.destinationref
    AND table1.amount = (0 - table2.amount)
) AS DuplicateResultsTable
GROUP BY transactionid1
ORDER BY transactionid1;

The result is that the DuplicateResultsTable provides rows containing matching (i.e. duplicate) transactions, but it also provides the same transaction id's in reverse the second time it matches the same pair, so the outer SELECT is there to group by the first transaction ID, which is done by using LEAST and GREATEST to make sure the two transactionid's are always in the same order in the results, which makes it safe to GROUP by the first one, thus eliminating all the duplicate matches. Ran through nearly a million records and identified 12,000+ matches in just under 2 seconds. Of course the transactionid is the primary index, which really helped.