How to delete duplicate data in mysql

As the amount of data continues to grow, we may encounter some duplicate data problems when using the MySQL database. When data is duplicated, in addition to taking up more storage space, it can also lead to confusing or inaccurate data. Therefore, it is important to know how to remove duplicate data. This article will introduce how to use some MySQL statements to delete duplicate data.

1. Use the DISTINCT keyword to query duplicate data

In MySQL, using the DISTINCT keyword can help us query different values. For example, we can query for all distinct usernames using the following statement:

SELECT DISTINCT username FROM users;

If we want to find recurring usernames, we can use the following query:

SELECT username, COUNT(*) FROM users
GROUP BY username
HAVING COUNT(*) > 1;

The above query will return Two columns of data: username and the number of times that username appears in the table. The HAVING clause can be used to filter out duplicate user names that appear more than 1 time.

2. Create a backup table using subqueries

We can use the INSERT INTO statement and subqueries to create a backup table so that we can remove duplicate data. This backup table will contain all the unique records we want to keep.

CREATE TABLE users_backup AS SELECT * FROM users WHERE 1 GROUP BY username;

This will create a backup table named "users_backup". The table will contain all user records but only unique usernames.

3. Delete duplicate records from the original table

Once we have created the backup table, we can delete the duplicate records from the original table. We can use DELETE statement and INNER JOIN to delete duplicate records.

DELETE u1 FROM users u1 INNER JOIN users u2
WHERE u1.id > u2.id AND u1.username = u2.username;

The above statement will delete duplicate records in the table and only retain the oldest created record. In the INNER JOIN clause, we join the table to itself so that it can be compared when records are deleted.

4. Reinsert the data from the backup table into the original table

Finally, we can use the INSERT INTO statement to reinsert the unique records from the backup table into the original table.

INSERT INTO users SELECT * FROM users_backup;

This will insert all records from the backup table into the original table, including the unique username record.

Summary

Removing duplicate data in MySQL can be complicated, however, the problem can be easily solved using the above method. We can use the DISTINCT keyword to query duplicate data and use the DELETE statement and INNER JOIN to delete duplicate records by comparison. Finally, we can also use the INSERT INTO statement to re-insert the unique records of the backup table into the original table. Through the above methods, we can clean up duplicate data in the database and improve data accuracy and efficiency.

The above is the detailed content of How to delete duplicate data in mysql. For more information, please follow other related articles on the PHP Chinese website!