How to Handle Duplicate Entries During MySQL Insertion?

MySQL Insertion with Duplicate Entry Handling

When inserting new records into a MySQL database with a unique field, it's common to encounter the "duplicate entry" error. To gracefully handle such errors, there are several approaches:

INSERT IGNORE

The INSERT ... IGNORE syntax can be used to prevent any error when attempting to insert a duplicate record. The insertion will be skipped without any error message.

REPLACE INTO

Unlike INSERT IGNORE, REPLACE INTO deletes any existing record with the same primary key before inserting a new one. This ensures that the table contains only one record per unique key.

INSERT ... ON DUPLICATE KEY UPDATE

This syntax allows you to perform an update on the existing record instead of inserting a new one. This can be useful for scenarios where you want to update specific column values while preserving the existing data.

Examples

Consider a table tbl with two columns, id and value. Let's assume we have a single record with id=1 and value=1.

• REPLACE INTO:

  REPLACE INTO tbl (id, value) VALUES (1, 50);

  This will result in a single record with id=1 and value=50.

• INSERT IGNORE:

  INSERT IGNORE INTO tbl (id, value) VALUES (1, 10);

  No action будет выполняться since there's already a record with id=1.

• INSERT ... ON DUPLICATE KEY UPDATE:

  INSERT INTO tbl (id, value) VALUES (1, 200) ON DUPLICATE KEY UPDATE value=200;

  This will update the existing record with id=1 to have value=200.

The above is the detailed content of How to Handle Duplicate Entries During MySQL Insertion?. For more information, please follow other related articles on the PHP Chinese website!