Best way to avoid duplicate entry into mysql database

Best way to avoid duplicate entry into mysql database

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

Best way to avoid duplicate entry into mysql database

When your application logic involves bulk importing data, relying on PHP's memory to track what has been inserted is often a trap for performance and scalability. In your specific scenario, you are attempting to dump 5,000 records where roughly half share the same pageId and name. Since the combination of these two columns must be unique, pushing the responsibility of deduplication entirely into your application loop introduces unnecessary overhead.

The most robust solution shifts this constraint to the database layer using a UNIQUE INDEX. This approach leverages the database engine's native capabilities to handle concurrency and consistency without burdening your PHP script with complex conditional logic.

Implementing the Unique Constraint

You need to define a composite unique index on the columns that constitute your logical primary key. In MySQL, this is distinct from the auto-increment id column, which serves as the physical row identifier.

ALTER TABLE your_table_name ADD UNIQUE KEY unique_page_name (pageId, name);

Once this constraint is in place, the database becomes the gatekeeper. If your PHP script attempts to insert a record where pageId and name already exist, MySQL will reject the operation with an ERROR 1062 (23000): Duplicate entry.

Your PHP code then needs to handle this specific error gracefully rather than treating it as a fatal failure. You can catch the exception thrown by the database connection or check the $e->getCode() for SQLSTATE[23000] (or 1062 in older versions).

try {
    $stmt = $pdo->prepare("INSERT INTO your_table_name (pageId, name) VALUES (:pageId, :name)");
    $stmt->execute([':pageId' => $row['pageId'], ':name' => $row['name']]);
} catch (PDOException $e) {
    if ($e->getCode() === 23000 || $e->getCode() === '23000') {
        // Duplicate detected, skip or update as needed.
        // Do not log this as an error; it is expected behavior.
        continue; 
    }
    throw $e;
}

The Trade-off: Upsert vs. Silent Skip

Handling the duplicate entry via exception catching works well, but it requires you to write try-catch blocks around every single insert statement. If your dataset contains 50% duplicates, you are effectively performing a successful operation only half the time while incurring the cost of rolling back the transaction and handling the exception.

A more performant alternative for high-volume imports is using INSERT ... ON DUPLICATE KEY UPDATE. This single statement attempts to insert the row; if the unique key conflict occurs, it executes an update instead. If your data model allows updating the id or other fields based on a duplicate, this is efficient because it reduces round-trips to the database and avoids exception handling overhead entirely.

INSERT INTO your_table_name (pageId, name) 
VALUES (:pageId, :name)
ON DUPLICATE KEY UPDATE name = VALUES(name), updated_at = NOW();

However, be wary of silent failures. If you are importing critical data where existence is mandatory (e.g., user credentials), silently updating or skipping duplicates might mask a logic error in your import script. In such cases, logging the duplicate count separately after the batch job completes is essential for auditing.

Why Not Check Before Insert?

A common instinct is to query the database first: SELECT 1 FROM table WHERE pageId = x AND name = y. If it returns a row, skip the insert. This approach is fundamentally flawed for bulk operations due to index usage and transaction isolation.

If you run a SELECT followed by an INSERT, you risk a race condition between two requests checking and inserting simultaneously, leading to duplicate entries anyway. Furthermore, executing 2,500 separate SELECT queries before your 2,500 INSERT queries doubles the network latency and database load for no safety gain. The unique index handles concurrency atomically at the storage engine level, making it far superior to application-side checks.

Conclusion

The definitive strategy for avoiding duplicates is establishing a composite unique index on pageId and name. This forces the database to enforce integrity. Whether you handle conflicts by catching exceptions or utilizing ON DUPLICATE KEY UPDATE, the constraint must exist in the schema first. Relying on application logic alone to prevent duplicates creates a fragile system that fails under concurrent load or when scaling across multiple servers.

Our network