How to Insert New Records into MySQL While Handling Duplicate Entries?

Inserting New Records While Ignoring Duplicate Entries in MySQL

In PHP, you may encounter a scenario where you need to insert multiple records into a database with unique fields. To ensure that only new records are inserted without encountering duplicate entry errors, consider the following approaches:

1. INSERT... IGNORE Syntax

This syntax allows MySQL to ignore duplicate entries without raising any errors. For example:

INSERT IGNORE INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

2. REPLACE INTO Syntax

Unlike INSERT... IGNORE, the REPLACE INTO syntax overwrites existing records with the same key value. This can be useful if you want to update the values of existing records:

REPLACE INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

3. INSERT... ON DUPLICATE KEY UPDATE Syntax

This syntax allows you to specify an update statement to be executed if a duplicate key is encountered. For example, to update the value of a column in a duplicate record:

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...) ON DUPLICATE KEY UPDATE column2 = newValue;

Examples

Suppose you have a table named tbl with columns id and value, with the record>

• REPLACE INTO

  REPLACE INTO tbl VALUES (1, 50);

  This would update the record with>

• INSERT IGNORE

  INSERT IGNORE INTO tbl VALUES (1, 10);

  This would ignore the duplicate entry and no record would be inserted.

• INSERT... ON DUPLICATE KEY UPDATE

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

  This would update the record with>

The above is the detailed content of How to Insert New Records into MySQL While Handling Duplicate Entries?. For more information, please follow other related articles on the PHP Chinese website!