Which record is preserved when using `ALTER IGNORE TABLE ADD UNIQUE` in MySQL?

MySQL: ALTER IGNORE TABLE ADD UNIQUE - Record Truncation Implications

An ALTER TABLE statement with the IGNORE clause is used to add a unique constraint to a table, potentially removing duplicate records. However, the specific record(s) retained can raise questions.

Question:

When executing the following statement:

ALTER IGNORE TABLE `my_table`
ADD UNIQUE (`type`, `owner`);

Which record will be preserved if there are multiple duplicate records with the same type and owner values?

Answer:

As of MySQL 5.7.4, the IGNORE clause is no longer supported and its use will result in an error.

However, in earlier versions of MySQL that allow the IGNORE clause:

• First Record Retained: Only the first row in the table with the unique key combination will be kept.
• Duplicate Rows Truncated: All other rows with the same unique key combination will be deleted.

According to the MySQL documentation, "first" likely refers to the record with the lowest primary key value. In your case, assuming the ID column is the primary key, the record with the smallest ID will be the one retained.

The above is the detailed content of Which record is preserved when using `ALTER IGNORE TABLE ADD UNIQUE` in MySQL?. For more information, please follow other related articles on the PHP Chinese website!