How Does `ALTER IGNORE TABLE ADD UNIQUE` Handle Duplicate Records in MySQL?

MySQL: ALTER IGNORE TABLE ADD UNIQUE - Which Record Remains?

When using the ALTER IGNORE TABLE ADD UNIQUE statement in MySQL, it's essential to understand its behavior regarding duplicate records with identical unique key values.

Specifically, in MySQL versions prior to 5.7.4, the IGNORE clause allowed duplicate records to be handled differently. For the ALTER TABLE operation you've provided:

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

If there are records with duplicate type and owner values, the behavior depends on the existence of a primary key.

With Primary Key (e.g., ID AUTO_INCREMENT PRIMARY KEY)

In this case, the record with the smallest ID will be preserved, while all other duplicate records with the same type and owner values will be deleted. The IGNORE clause instructs MySQL to ignore any conflicts caused by duplicate key values.

Without Primary Key

If no primary key exists, the exact behavior of ALTER IGNORE TABLE is more ambiguous. Different MySQL versions may handle this situation differently, so it's generally recommended to avoid using IGNORE without a primary key.

In MySQL 5.7.4 and Later

It's important to note that the IGNORE clause for ALTER TABLE has been removed from MySQL 5.7.4 onwards. Attempting to use IGNORE in these versions will result in an error.

The above is the detailed content of How Does `ALTER IGNORE TABLE ADD UNIQUE` Handle Duplicate Records in MySQL?. For more information, please follow other related articles on the PHP Chinese website!