Three Efficient Methods for Handling Duplicate Inserts in MySQL: IGNORE, REPLACE, and ON DUPLICATE KEY UPDATE

In database development, encountering unique constraint conflicts during batch data insertion is a common issue. Traditional approaches like query-before-insert are not only inefficient but may also cause race conditions. MySQL provides three built-in mechanisms to handle this situation elegantly, each with its unique application scenarios and behavioral characteristics.

INSERT IGNORE: Silently Ignoring Duplicate Entries

The INSERT IGNORE syntax is the most straightforward solution. When an insert operation encounters a unique key conflict, MySQL silently skips that row without reporting an error. Its execution principle is: MySQL checks unique constraints before attempting insertion, and if a conflict is detected, marks the row as ignored and continues processing subsequent rows. This method is particularly suitable for batch insertion scenarios, provided data integrity is not compromised.

Implementation example in PHP:

$sql = "INSERT IGNORE INTO users (email, name) VALUES (?, ?), (?, ?), (?, ?)";
$stmt = $pdo->prepare($sql);
$stmt->execute([
    'user1@example.com', 'User One',
    'user2@example.com', 'User Two',  // Assume this email already exists
    'user3@example.com', 'User Three'
]);
// After execution, only user1 and user3 are inserted, user2 is silently ignored

It's important to note that INSERT IGNORE not only ignores unique key conflicts but also other recoverable errors like data type conversion issues. Therefore, caution is needed in scenarios requiring strict data validation.

REPLACE INTO: Replacing Existing Records

The REPLACE INTO operation, when encountering a unique key conflict, first deletes the existing record and then inserts the new one. This is essentially a combination of DELETE followed by INSERT, thus triggering corresponding delete and insert triggers. This method is suitable for scenarios requiring complete overwriting of old data.

Consider the following example table structure:

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100) UNIQUE,
    stock INT DEFAULT 0
);

PHP code using REPLACE:

$sql = "REPLACE INTO products (id, name, stock) VALUES (1, 'Laptop', 50)";
// If id=1 or name='Laptop' already exists, the original record is deleted and the new one inserted

Potential issues with REPLACE include: if the table has an auto-increment primary key, replacement may change the primary key value; if there are foreign key constraints, cascading deletions may be triggered. Therefore, comprehensive impact assessment is needed in complex database designs.

INSERT...ON DUPLICATE KEY UPDATE: Conditional Updates

This is the most flexible method, performing an update operation instead of an insert when a duplicate key is detected. The syntax allows specifying which fields to update and can even perform calculations based on original values. This method is particularly useful in scenarios like statistical counting and status updates.

Advanced usage example:

$sql = "INSERT INTO page_views (page_id, view_date, view_count) 
        VALUES (?, CURDATE(), 1)
        ON DUPLICATE KEY UPDATE 
        view_count = view_count + 1,
        last_viewed = NOW()";
// If a record for the same page and date already exists, increment view count and update last viewed time

Another advantage of ON DUPLICATE KEY UPDATE is the ability to reference the VALUES() function during updates to obtain the originally intended insertion value:

INSERT INTO inventory (product_id, quantity) 
VALUES (100, 10)
ON DUPLICATE KEY UPDATE 
quantity = quantity + VALUES(quantity);

Performance Comparison and Selection Recommendations

From a performance perspective: INSERT IGNORE is typically the fastest as it only needs to check constraints without additional operations; REPLACE is the slowest as it involves both delete and insert operations; ON DUPLICATE KEY UPDATE falls between, depending on the complexity of the update operation.

Selection recommendations:

• Use INSERT IGNORE when only needing to skip duplicate data without concern for existing content
• Use REPLACE INTO when complete replacement of old records is required
• Use ON DUPLICATE KEY UPDATE when specific business logic needs to be executed based on duplicates

In actual PHP development, it's recommended to combine prepared statements to prevent SQL injection and use transactions to ensure consistency in batch operations. For extremely large batch insertions, consider using the LOAD DATA INFILE command with appropriate options.