How Can I Remove Duplicate Rows in MySQL Without Using External Scripts?

Removing Duplicate Rows in MySQL: A Database-Only Approach

This guide demonstrates how to efficiently remove duplicate rows from a MySQL table based on specific columns (e.g., site_id, title, company) without relying on external scripts. While GROUP BY can identify duplicates, it necessitates a separate deletion process. A more direct method leverages MySQL's UNIQUE index functionality.

The UNIQUE Index Method (Deprecated)

Previously, adding a UNIQUE index with the IGNORE keyword offered a solution. MySQL would automatically reject duplicate rows during index creation. The syntax would be:

<code class="language-sql">ALTER IGNORE TABLE jobs
ADD UNIQUE INDEX idx_name (site_id, title, company);</code>

Important Note: This ALTER IGNORE TABLE approach is deprecated in MySQL 5.6 and removed entirely in MySQL 5.7 and later versions. Therefore, this method is not reliable for current MySQL installations.

(Alternative Method Required for Modern MySQL Versions) To remove duplicates in newer MySQL versions, a multi-step approach using ROW_NUMBER() is necessary. This will require a more complex query, but it remains a database-only solution. Further instructions on this alternative method are available in other resources.

The above is the detailed content of How Can I Remove Duplicate Rows in MySQL Without Using External Scripts?. For more information, please follow other related articles on the PHP Chinese website!