如何防止重复记录？求插入时非unique约束的去重方案

Hey there! I get that avoiding UNIQUE constraints adds some extra hoops to jump through, but there are solid, reliable ways to tackle both preventing duplicates in the database overall and stopping them at the insertion step. Let's break this down:

一、 General Strategies to Prevent Duplicate Records in the Database #

Even if you can't use UNIQUE constraints, these methods help keep your data clean:

• Scheduled Duplicate Cleanup Jobs
  Run periodic tasks to scan for and remove duplicates. This works as a safety net for any slips that get past insertion checks. For example, in MySQL, you can use a query like this to keep only the earliest (or latest) record for each duplicate group:

  DELETE FROM your_table 
  WHERE id NOT IN (
      SELECT MIN(id) 
      FROM your_table 
      GROUP BY column1, column2 -- Replace with your unique identifier columns
      HAVING COUNT(*) > 1
  );
  

  Pro tip: Run these jobs during low-traffic hours to avoid locking issues and performance hits.

• Database Triggers for Pre-Write Checks
  Create a trigger that runs before an INSERT or UPDATE operation to check for existing duplicates. If a duplicate is found, it can throw an error or block the operation. Here's a MySQL example:

  DELIMITER //
  CREATE TRIGGER block_duplicate_records BEFORE INSERT ON your_table
  FOR EACH ROW
  BEGIN
      IF EXISTS (
          SELECT 1 FROM your_table 
          WHERE column1 = NEW.column1 AND column2 = NEW.column2
      ) THEN
          SIGNAL SQLSTATE '45000' 
          SET MESSAGE_TEXT = 'Duplicate record detected - insertion blocked';
      END IF;
  END //
  DELIMITER ;
  

  Note: Trigger syntax varies across databases (PostgreSQL, SQL Server, etc.), so adjust accordingly. Also, keep an eye on trigger performance for high-write tables.

二、 Preventing Duplicates During Insertion (No UNIQUE Constraints) #

The biggest challenge here is avoiding race conditions when multiple processes try to insert the same record at the same time. These methods address that:

• Atomic "Insert If Not Exists" Queries
  Combine your check and insert into a single atomic SQL statement. This eliminates the window where two concurrent processes could both check, find no duplicate, and then insert. For example:

  INSERT INTO your_table (column1, column2, column3)
  SELECT 'value1', 'value2', 'value3'
  WHERE NOT EXISTS (
      SELECT 1 FROM your_table 
      WHERE column1 = 'value1' AND column2 = 'value2'
  );
  

  This works because the entire SELECT + INSERT runs as one atomic operation—no other transaction can modify the table between the check and insert.

• Pessimistic Locking for Critical Paths
  If you need to run more complex logic before inserting, use row-level locking to block concurrent access. First, query the record with a lock, then proceed only if it doesn't exist:

  -- Start a transaction
  BEGIN TRANSACTION;
  
  -- Lock the potential duplicate row (if it exists)
  SELECT * FROM your_table 
  WHERE column1 = ? AND column2 = ?
  FOR UPDATE;
  
  -- If no row was returned, insert the new record
  INSERT INTO your_table (column1, column2) VALUES (?, ?);
  
  -- Commit the transaction
  COMMIT;
  

  This ensures that only one transaction can check and insert for the same key at a time. Just be cautious with lock duration to avoid holding locks longer than needed.

• Distributed Locks for Multi-Instance Systems
  If your app runs on multiple servers, database-level locks might not be enough. Use a distributed lock system (like Redis or ZooKeeper) to serialize access to the insertion logic. Here's a rough workflow:

  1. Generate a unique lock key based on the columns that define a duplicate (e.g., duplicate_check:col1_value:col2_value).
  2. Acquire the lock with a reasonable timeout (to avoid deadlocks).
  3. Perform the "check if exists" query.
  4. Insert the record if no duplicate is found.
  5. Release the lock immediately after.

• Application-Level Deduplication (As a First Step)
  Before even hitting the database, remove duplicates from your input data. For example, if you're processing a batch of records, use a hash set to filter out duplicates based on your unique columns. This reduces unnecessary database calls, though it won't prevent cross-instance duplicates—always pair this with a database-level check.

Quick Recap #

• For ongoing database cleanliness: Use scheduled cleanup jobs and triggers.
• For safe insertion: Prioritize atomic "insert if not exists" queries for single-instance apps, or distributed locks plus atomic queries for multi-instance systems.
• Always combine application-level checks with database-level safeguards to cover all edge cases.

内容的提问来源于stack exchange，提问作者Anand