MySQL - ignore insert error: duplicate entry
Handling unique constraint violations during batch inserts is a common scenario when migrating data, seeding environments, or syncing records from external sources. The goal is straightforward: you want new rows added to the table while silently ignoring attempts to insert duplicates that already exist in the database. In your specific PHP use case, relying on SELECT before every INSERT to check for existence is a pattern known as N+1 queries. This approach creates a linear performance bottleneck where you execute one query per row you intend to insert. As the batch size grows, this strategy quickly becomes a scalability issue, especially if your database server is under load.
MySQL provides a native mechanism to handle this requirement directly at the SQL level: the INSERT IGNORE statement. This command tells the storage engine to attempt the insertion but discard any rows that violate unique constraints without raising an error. When you use INSERT IGNORE, MySQL attempts to insert the row; if a duplicate key is found, it simply returns success with no rows affected, rather than throwing a "Duplicate entry" exception.
From a PHP perspective, this translates to changing how you handle the result of your database interaction. If you are using raw PDO or mysqli, executing an INSERT IGNORE statement will return a boolean success even when duplicates exist. You can then check $affectedRows or rely on the fact that the script continues execution without halting due to an exception. For example:
$query = "INSERT IGNORE INTO users (email, name) VALUES (?, ?)";
$stmt = $pdo->prepare($query);
foreach ($records as $record) {
$stmt->execute([$record['email'], $record['name']]);
}
This approach is significantly more efficient than the SELECT/INSERT pattern because it consolidates two round-trips into one atomic operation per row. It reduces network latency and database CPU usage by eliminating redundant reads.
However, there are critical trade-offs to consider regarding data integrity. By using INSERT IGNORE, you are explicitly choosing a "best effort" update strategy rather than enforcing strict uniqueness at the application level. If your application logic requires that every record in the batch is unique, silently ignoring duplicates might mask data quality issues upstream. For instance, if an external API sends duplicate user emails because their system hasn't updated recently, INSERT IGNORE will accept those duplicates without alerting you to the synchronization failure.
If you need to know exactly how many rows were actually inserted versus how many were ignored, you must inspect the number of affected rows after execution. In PDO, this is accessible via $stmt->rowCount(). A return value of zero indicates that all rows in that specific batch were already present in the database.
Another alternative often discussed in these scenarios is the ON DUPLICATE KEY UPDATE clause. While functionally similar for ignoring errors, this syntax implies an update operation rather than a strict ignore. If you do not intend to modify existing columns based on the duplicate key, INSERT IGNORE is generally cleaner and semantically more accurate.
For developers working within the Laravel ecosystem, this pattern maps directly to how the query builder handles database interactions. While Laravel's default behavior throws exceptions on constraint violations by default, understanding the underlying SQL mechanics allows you to bypass this when necessary. You can construct raw queries using DB::statement() to execute INSERT IGNORE logic if your version of Laravel does not provide a convenient shortcut for ignoring duplicates in bulk operations.
Ultimately, the decision rests on your data consistency requirements. If duplicate rows are harmless and you simply want to populate the table with available data, INSERT IGNORE is the robust, performant solution. If duplicates indicate a process failure that needs investigation, relying solely on SQL-level ignores might be too permissive for your architecture.
