Why MySQL Unique Indexes Still Allow Duplicates and How to Fix Them

Introduction

Recently I encountered a pitfall: a mysql8 InnoDB table with a unique index still allowed duplicate data. This article shares the experience and explores interesting aspects of unique indexes.

1. Reproducing the Issue

To prevent duplicate product groups, I created an anti‑duplicate table:

CREATE TABLE `product_group_unique` (
  `id` bigint NOT NULL,
  `category_id` bigint NOT NULL,
  `unit_id` bigint NOT NULL,
  `model_hash` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `in_date` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

Then I added a unique index on category_id, unit_id and model_hash:

alter table product_group_unique add unique index ux_category_unit_model(category_id,unit_id,model_hash);

After inserting data, duplicate rows still appeared.

2. Unique Index Fields Containing NULL

If model_hash is NULL, the uniqueness constraint does not take effect. Inserting rows where model_hash is NULL succeeds even when other indexed columns match, leading to duplicate records.

All fields that participate in a unique index must be NOT NULL; otherwise MySQL may ignore the uniqueness check for NULL values.

3. Logical Delete Tables and Unique Indexes

Logical deletion (using an update to set a delete_status flag) makes it difficult to add a unique index because deleted rows still exist in the table.

3.1 Incremental Delete Status

Instead of a binary flag, use an incremental delete_status value (1, 2, 3, …). Each deletion increments the status, ensuring that the combination of indexed columns remains unique.

3.2 Add a Timestamp Field

Add a timestamp column and include it in the unique index. Each logical delete writes the current timestamp, guaranteeing uniqueness even for repeated deletions.

3.3 Add a Delete ID Field

Introduce a delete_id primary‑key field. When a row is logically deleted, set delete_id to the row’s primary key, and include it in the unique index along with other columns.

4. Adding a Unique Index to Historical Duplicate Data

For tables that already contain duplicate historical records, create a new anti‑duplicate table and migrate distinct rows, or add a delete_id column, assign unique values to existing duplicates, and then create the composite unique index.

5. Unique Index on Large Columns

When indexed columns are large (e.g., a long model field), the index size may exceed MySQL’s limit (1000 bytes for a unique key). Solutions include:

5.1 Add a Hash Column

Store a short hash of the large column and index the hash instead of the full value. Be aware of possible hash collisions.

5.2 Do Not Add a Unique Index

Rely on application‑level controls such as single‑threaded inserts or MQ‑based serialization to prevent duplicates.

5.3 Use Redis Distributed Locks

Generate a hash from the combination of indexed fields and acquire a Redis lock on that hash before inserting, reducing the chance of duplicate inserts under concurrency.

6. Batch Insertion

For bulk inserts, relying on MySQL’s unique index is far more efficient than acquiring per‑row Redis locks. A single INSERT ... ON DUPLICATE KEY IGNORE statement can handle thousands of rows while the database enforces uniqueness.