How to Efficiently Handle Duplicate Entries During MySQL Inserts in PHP?

MySQL - Ignore Duplicate Entry Insertions

Scenario: Inserting Unique Records Without Errors

In PHP, when inserting a batch of records into a database with a unique field, it's desirable to ignore duplicate entry errors. Instead, you want to ensure that only new records are inserted without any interruptions.

Avoiding SELECT-INSERT Checks

Performing a SELECT query to check for existing entries before inserting is not an efficient approach. Here are alternative methods using MySQL's built-in functionality:

1. INSERT... IGNORE:

   This syntax allows you to insert a record and ignore it if a duplicate already exists. No error will be thrown.

   INSERT IGNORE INTO tbl (field1, field2, ...) VALUES (...);

2. REPLACE INTO:

   This syntax overwrites an existing record with the new one if the primary key matches. An error will be raised if the primary key is not unique.

   REPLACE INTO tbl (field1, field2, ...) VALUES (...);

3. INSERT... ON DUPLICATE KEY UPDATE:

   This syntax inserts a new record or performs an update if a duplicate primary key is found.

   INSERT INTO tbl (field1, field2, ...) VALUES (...) ON DUPLICATE KEY UPDATE field2 = NEW.field2;

Examples

Assuming a table named 'tbl' with columns 'id' and 'value', initially containing a record with 'id' = 1 and 'value' = 1:

• REPLACE INTO:

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

  Result: Overwrites the existing record with 'value' = 50.

• INSERT IGNORE:

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

  Result: Ignores the duplicate entry since 'id' 1 already exists.

• INSERT... ON DUPLICATE KEY UPDATE:

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

  Result: Updates the existing record with 'value' = 200 since 'id' 1 already exists.

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