How to Remove Duplicate Rows in MySQL Using Only SQL Queries?

Remove duplicate rows in MySQL without external scripts

In some cases, you may need to remove duplicate rows in a MySQL table based on a specific combination of columns. The following explores how to achieve this using only SQL queries, avoiding server-side scripts.

Background

As stated in the initial question, the table contains multiple columns, including 'id', 'url', 'title', 'company', and 'site_id'. The goal is to remove those rows where 'title', 'company' and 'site_id' have the same value.

Solution

An effective way to remove duplicate rows is to use a UNIQUE index on these three columns. The syntax for creating such an index is as follows:

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

Please note the keyword 'IGNORE' is used in the statement. This tells MySQL to ignore duplicate rows when creating the index. This means these duplicate rows will be removed from the table.

Additional benefits

In addition to removing existing duplicate rows, adding a UNIQUE index prevents duplicate rows from being inserted in the future. Any attempt to insert a row where a combination of 'site_id', 'title' and 'company' already exists will result in an error.

Note:

Please note that this feature has been removed in MySQL 5.7 and later. Therefore, this solution no longer works with these MySQL versions.

The above is the detailed content of How to Remove Duplicate Rows in MySQL Using Only SQL Queries?. For more information, please follow other related articles on the PHP Chinese website!