How to Handle Duplicate Entries During Mass Insertion in MySQL?

MySQL: Ignoring Duplicate Entry Errors during Mass Insertion

When inserting multiple records into a database with a unique field, handling duplicate entries becomes necessary. In PHP, you may encounter the challenge of preventing errors due to duplicate entries.

One approach is to perform a SELECT operation before each INSERT, checking for the existence of the entry. However, this method can be inefficient for large datasets. Fortunately, MySQL offers several solutions to ignore duplicate entries during insertion.

INSERT...IGNORE Syntax

The simplest method is to use the INSERT...IGNORE syntax. This instructs MySQL to discard duplicate entries without raising an error. For example:

INSERT IGNORE INTO tbl_name (field1, field2) VALUES (value1, value2);

REPLACE INTO Syntax

Another option is the REPLACE INTO syntax. This will overwrite existing records with the same primary key. For instance:

REPLACE INTO tbl_name (field1, field2) VALUES (value1, value2);

INSERT...ON DUPLICATE KEY UPDATE Syntax

Finally, you can use the INSERT...ON DUPLICATE KEY UPDATE syntax to perform an update on an existing record instead of inserting a duplicate. For example:

INSERT INTO tbl_name (field1, field2) VALUES (value1, value2) ON DUPLICATE KEY UPDATE field2 = value2;

Examples

Consider a table named tbl with two columns, id and value. Assuming the table initially contains a single record with id=1 and value=1, the following code demonstrates the effects of the different syntaxes:

-- REPLACE INTO
REPLACE INTO tbl VALUES (1, 50);

-- INSERT IGNORE
INSERT IGNORE INTO tbl VALUES (1, 10);

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

After these operations, the table contains a single record with id=1 and value=200.

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