How Can I Efficiently Delete Duplicate Rows in MySQL While Keeping One Record?

MySQL Duplicate Row Removal Guide: Efficiently Retain Unique Records

This article explains how to remove duplicate data from a MySQL table and retain one record from each set of duplicate data. While it is possible to filter out unique values ​​using SELECT DISTINCT, this article focuses on how to achieve this using the DELETE command.

Important Tips:

Be sure to back up your tables before performing any delete operations to prevent accidental data loss.

Keep the record with the smallest ID:

To keep the row with the smallest ID among duplicates, run the following SQL statement:

<code class="language-sql">DELETE n1 FROM names n1, names n2 WHERE n1.id > n2.id AND n1.name = n2.name;</code>

Keep the record with the largest ID:

To retain the row with the largest ID among duplicates, run the following SQL statement:

<code class="language-sql">DELETE n1 FROM names n1, names n2 WHERE n1.id < n2.id AND n1.name = n2.name;</code>

Other notes:

Efficiency warning:

The above method is not efficient and may cause database connection overload.

Integrity requirements:

MUST contain the AND n1.id < n2.id (or AND n1.id > n2.id) clause. Ignoring this condition may cause all rows in the table to be deleted.

Alternative:

For better performance, consider using the following INSERT and DISTINCT queries:

<code class="language-sql">INSERT INTO tempTableName (cellId, attributeId, entityRowId, value)
SELECT DISTINCT cellId, attributeId, entityRowId, value
FROM tableName;</code>

You can then delete the original table and rename the temporary table. This method avoids direct deletion of data, thereby improving efficiency and security.

The above is the detailed content of How Can I Efficiently Delete Duplicate Rows in MySQL While Keeping One Record?. For more information, please follow other related articles on the PHP Chinese website!