How to remove all duplicate records in MySQL table?-Mysql Tutorial-php.cn

How to remove all duplicate records in MySQL table?

To delete duplicate records from a table, we can use the DELETE command. Now let's create a table.

mysql> create table DuplicateDeleteDemo -> ( -> id int, -> name varchar(100) -> ); Query OK, 0 rows affected (0.60 sec)

Copy after login

Insert records into the table "DuplicateDeleteDemo": Here, we add "John" as a duplicate record 3 times.

mysql> insert into DuplicateDeleteDemo values(1,'John'); Query OK, 1 row affected (0.11 sec) mysql> insert into DuplicateDeleteDemo values(1,'John'); Query OK, 1 row affected (0.14 sec) mysql> insert into DuplicateDeleteDemo values(2,'Johnson'); Query OK, 1 row affected (0.13 sec) mysql> insert into DuplicateDeleteDemo values(1,'John'); Query OK, 1 row affected (0.14 sec)

Copy after login

To display all records, use the SELECT statement.

mysql> select *from DuplicateDeleteDemo;

Copy after login

The following is the output containing duplicate records.

+------+---------+ | id | name | +------+---------+ | 1 | John | | 1 | John | | 2 | Johnson | | 1 | John | +------+---------+ 4 rows in set (0.00 sec)

Copy after login

In the above output, there are 4 records in the table, 3 of which are duplicates.

To delete duplicate records, use DELETE.

mysql> delete from DuplicateDeleteDemo where id=1; Query OK, 3 rows affected (0.19 sec)

Copy after login

To check if a record has been deleted, let's display all records again.

mysql> select *from DuplicateDeleteDemo;

Copy after login

The following output shows that all duplicate records have been removed.

+------+---------+ | id | name | +------+---------+ | 2 | Johnson | +------+---------+ 1 row in set (0.00 sec)

Copy after login

The above is the detailed content of How to remove all duplicate records in MySQL table?. For more information, please follow other related articles on the PHP Chinese website!