Find Duplicate Rows in MySQL

[suttercain]

Hi everyone,

 

I have a table that contains about 18,000 records. The table has a primary key (unique id) and 21 columns.

 

Is there an easy way to locate duplicate rows? I would like to find a rows with the same data, with the exception of the primary key.

 

Thanks.

 

SC

[GingerRobot]

So you want to find rows where the 20 columns (all but the primary key) are duplicates?

 

<?php
$fields = array('field1','field2','field3');//list your fields
$sql = "SELECT * FROM users WHERE (`".implode('`,`',$fields)."`) IN(SELECT ".implode('`,`',$fields)." FROM users GROUP BY first,last)";
$result = mysql_query($sql) or die(mysql_error());
//show results
?>

 

If you dont also want to return the primary key, just use the inner query.