How to Handle Duplicate Entries When Inserting Data into MySQL?

MySQL - Handling Duplicate Entries on Insert

When inserting records into a database that contains unique fields, it's common to encounter duplicate entry errors. These errors can disrupt batch insert operations, where only the new records should be inserted.

Insert with Ignore

MySQL provides the INSERT...IGNORE syntax, which allows insertions to proceed without generating an error when a duplicate entry is encountered. For example:

INSERT IGNORE INTO tbl (id, name) VALUES (1, 'John Doe');

If an entry with id=1 already exists, the statement will execute successfully without inserting a new row.

Replace

The REPLACE INTO syntax overwrites an existing record with a new one that has the same primary key. This can be useful if you want to ensure that the latest data is stored in the table:

REPLACE INTO tbl (id, name) VALUES (1, 'Jane Doe');

In this case, if id=1 exists, the old record will be deleted and the new one will be inserted.

Insert with ON DUPLICATE KEY UPDATE

The INSERT...ON DUPLICATE KEY UPDATE syntax allows you to specify an action to perform if a duplicate entry is encountered. For example, you can update the existing record:

INSERT INTO tbl (id, name) VALUES (1, 'Alice Smith') ON DUPLICATE KEY UPDATE name = 'Alice Smith';

If id=1 exists, the existing record's name will be updated to 'Alice Smith'.

Examples

Consider a table named tbl with columns id and value, containing one row: (1, 1).

• REPLACE:

  REPLACE INTO tbl VALUES (1, 50);

  Result: (1, 50) (replaces the existing record)

• INSERT IGNORE:

  INSERT IGNORE INTO tbl VALUES (1, 10);

  Result: No change (ignores the duplicate entry)

• INSERT WITH ON DUPLICATE KEY UPDATE:

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

  Result: (1, 200) (updates the existing record)

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