ALTER IGNORE TABLE ADD UNIQUE: What Happens to Duplicate Records in MySQL?

ALTER IGNORE TABLE ADD UNIQUE: What Happens to Duplicate Records?

When altering a table using ALTER IGNORE TABLE ADD UNIQUE, the behavior of MySQL in handling duplicate records has evolved over its versions.

MySQL 5.7.4 and Later:

In MySQL 5.7.4 and subsequent versions, the IGNORE clause is no longer available for ALTER TABLE operations, and its use results in an error.

MySQL Versions Prior to 5.7.4:

In earlier versions of MySQL that supported the IGNORE clause, its behavior was as follows:

• The first record with the duplicate key would be retained.
• All subsequent records with duplicate keys would be deleted.

This means that the record with the smallest primary key (ID in your case) would be preserved, while all others would be discarded. This is because the primary key is auto-incremented and therefore determines the order of record insertion.

It's important to note that the IGNORE clause also affects the truncation of incorrect values. Any values that violate constraints are truncated to the closest acceptable value.

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