How to check if a value already exists to avoid duplicates?

How to check if a value already exists to avoid duplicates?

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

Stefan Bogdanescu

Founder & Senior Architect

Preventing Duplicate URLs in MySQL with PHP

You have a table storing URLs and need to ensure that inserting a new record doesn't result in duplicates. The most naive approach is often to query the database first, check the result count, and then decide whether to insert or ignore. While this logic holds up in theory, it introduces unnecessary round trips between your application and the database server. Every extra SELECT statement adds latency and increases load on the database engine.

A more efficient strategy relies on leveraging MySQL's built-in capabilities for duplicate detection directly within the INSERT operation. Modern versions of MySQL (8.0.19+) allow you to use a DUPLICATE KEY UPDATE. This feature lets you attempt an insert, and if a unique key violation occurs, it automatically handles the conflict by updating the existing row instead of throwing an exception.

To implement this, your table must have a UNIQUE constraint on the URL column. If such a constraint exists, you can write your insertion logic like this:

INSERT INTO urls (url) VALUES ('https://example.com')
ON DUPLICATE KEY UPDATE url = url;

In this example, if the URL already exists in the table, the ON DUPLICATE KEY UPDATE clause triggers. The statement url = url is a no-op; it effectively does nothing to the data but successfully returns an affected row count of 1 instead of failing. If the URL is new, the insert happens as normal, and the affected rows also return 1.

Checking the number of affected rows allows your PHP script to determine if an update occurred or if a fresh insert happened. This pattern is particularly useful when you want to treat duplicates not as errors, but as signals that data already exists and perhaps needs refreshing.

However, there is an important distinction to make between this behavior and INSERT IGNORE. The IGNORE clause silently discards the duplicate without updating any columns. If your goal is strictly to prevent duplicates by doing nothing when a match is found, INSERT IGNORE is the correct choice:

INSERT IGNORE INTO urls (url) VALUES ('https://example.com');

With this syntax, if the URL exists, the row is simply skipped. The affected rows count will be 0. If you rely on checking the affected rows to know whether an action took place, IGNORE provides a clear signal: 0 means nothing changed, while any number greater than 0 means a new record was created.

It is crucial to ensure that the url column is defined with a UNIQUE index or PRIMARY KEY constraint. Without this physical constraint at the database level, MySQL cannot detect duplicates efficiently during an insert operation. Attempting to enforce uniqueness purely in application code by querying the table first defeats the purpose of database-level constraints and leaves the system vulnerable to race conditions where two concurrent requests might both check for existence before either inserts, resulting in a duplicate entry anyway.

If you are building this functionality within the Laravel framework, you can wrap this logic cleanly using Eloquent's upsert method, which abstracts the SQL syntax mentioned above:

Url::createOrFirst(['url' => 'https://example.com']);

This method attempts to insert the record and updates it if a unique constraint violation occurs, matching the behavior of ON DUPLICATE KEY UPDATE. Always verify that your model's $guarded property or fillable attributes allow you to set the URL field without unintended side effects.

Ultimately, shifting duplicate checking from application logic to database constraints ensures consistency, performance, and scalability. Whether you choose to update existing records or ignore them entirely depends on your specific data integrity requirements, but relying on unique keys is non-negotiable for enforcing uniqueness effectively.

Our network