Remove Duplicate Entry on MySQL Database

Removing duplicate entry on MySQL database was a boring time and wash lot of time. Especially if the database containing thousand of row. Since there are no MySQL function to do this, some webmaster do it using loop and of course it need lot of memory resource on server.

There is one extremely quick and painless way to remove duplicate rows from a MySQL database table. It can all be done with three manual MySQL queries. Create a file on your server then copy and paste the PHP code bellow. Change the bold text with parameter you need and run it.

// function to remove duplicate entry
function removeDuplicate($old_table, $temp_table, $col_name) {
$st = microtime(true); // start timer

// connect to database

$con = mysql_connect(“localhost”, “database user name“,”database password“);
if (!$con)  {
die(‘Upss…<br /> server temporary down.’);
}
mysql_select_db(“database name“, $con);

// duplicate table with unique value
mysql_query(“CREATE TABLE “. $temp_table .” as SELECT * FROM “. $old_table .” GROUP BY “. $col_name);

// drop old table with duplicate entry
mysql_query(“DROP TABLE “. $old_table);

// rename temporary table to old table name
mysql_query(“RENAME TABLE “. $temp_table .” TO “. $old_table);

// stop timer
$et = microtime(true) – $st;
echo ‘<br>Proccesing time: ‘. number_format($et, 4, ‘.’, ‘,’) .’ seconds’;
}

// execute the function, col_name is the colum with duplicate entry
removeDuplicate(‘old_table‘, ‘temp_table‘, ‘col_name‘);

Note: When you done using this script, put die(); on first line or delete the file.

How the script work?

So, there are 3 steps, and therefore 3 SQL statements:

Step 1: Move the table content into a temporary table as distinct colum using GROUP BY

CREATE TABLE temp_table as
SELECT * FROM old_table GROUP BY [column to remove duplicates by];

Step 2: Delete the old table
We no longer need the table with all the duplicate entries, so drop it!

DROP TABLE old_table;

Step 3: Rename the temp_table to the name of the old_table

RENAME TABLE temp_table TO old_table;

Category: Tips and Tricks
Tags: | |

Newer post:
Older post: