Remove duplicate record in table

In this post, we are going to see how to remove duplicate records from table.

Create a test table

mysql> CREATE TABLE test_table (`id` int(11) DEFAULT NULL,`name` varchar(10) DEFAULT NULL);

Insert duplicate values in test table.

mysql> insert into test_table values (1,'aaa'),(1,'aaa'),(1,'aaa'),(1,'bbb'),(1,'bbb'),(1,'bbb');

mysql> select * from test_table;
+------+------+
| id | name |
+------+------+
| 1 | aaa |
| 1 | aaa |
| 1 | aaa |
| 1 | bbb |
| 1 | bbb |
| 1 | bbb |
+------+------+

Remove duplicate rows from test table

mysql> alter IGNORE table test_table add UNIQUE key (id,name);
Records: 6 Duplicates: 4 Warnings: 0

mysql> select * from test_table ;
+------+------+
| id | name |
+------+------+
| 1 | aaa |
| 1 | bbb |
+------+------+