无需唯一键删除MySQL中近两日新增的重复用户数据

Got it, let's work through this problem carefully—since you can't rely on unique keys and need to keep old duplicates tied to dependent data, here's a targeted approach to clean up the recent duplicates without risking your existing关联 data:

1. 先明确重复判定规则 #

First, you need to define what counts as a duplicate record. This depends on your business logic—common examples are combining fields like name + id_card, email + phone, or whatever uniquely identifies a person in your system. Make sure this is accurate, otherwise you might delete valid records.

2. 用窗口函数标记待删除的重复项 #

We'll use a window function to rank records within each duplicate group, focusing only on entries created in the last two days. This lets us keep the earliest entry (or whichever you want to retain) and mark the rest for deletion.

先查询待删除的记录（安全验证第一步） #

Run this first to confirm you're targeting the right records:

WITH duplicate_candidates AS (
    SELECT 
        id,
        created_at,
        -- Replace `name, id_card` with your actual duplicate-checking fields
        ROW_NUMBER() OVER (
            PARTITION BY name, id_card 
            ORDER BY created_at ASC -- Keeps the earliest record in each duplicate group
        ) AS record_rank
    FROM people
    -- Filter for records created in the last 2 days; adjust the date condition for your DB
    WHERE created_at >= DATE_SUB(NOW(), INTERVAL 2 DAY)
)
SELECT id, created_at FROM duplicate_candidates WHERE record_rank > 1;

执行删除操作 #

Once you've verified the results are correct, run the delete:

WITH duplicate_candidates AS (
    SELECT 
        id,
        ROW_NUMBER() OVER (
            PARTITION BY name, id_card 
            ORDER BY created_at ASC
        ) AS record_rank
    FROM people
    WHERE created_at >= DATE_SUB(NOW(), INTERVAL 2 DAY)
)
DELETE FROM people
WHERE id IN (SELECT id FROM duplicate_candidates WHERE record_rank > 1);

数据库适配调整 #

• Oracle: Use SYSDATE - 2 instead of DATE_SUB(NOW(), INTERVAL 2 DAY), and wrap the subquery in an extra layer:

  DELETE FROM people
  WHERE id IN (
      SELECT id
      FROM (
          SELECT 
              id,
              ROW_NUMBER() OVER (
                  PARTITION BY name, id_card 
                  ORDER BY created_at ASC
              ) AS record_rank
          FROM people
          WHERE created_at >= SYSDATE - 2
      )
      WHERE record_rank > 1
  );
  

• SQL Server: Use DATEADD(day, -2, GETDATE()) and consider batch deletes if you're dealing with thousands of records to avoid locking issues:

  WHILE EXISTS (
      SELECT 1
      FROM (
          SELECT 
              ROW_NUMBER() OVER (
                  PARTITION BY name, id_card 
                  ORDER BY created_at ASC
              ) AS record_rank
          FROM people
          WHERE created_at >= DATEADD(day, -2, GETDATE())
      ) AS duplicates
      WHERE record_rank > 1
  )
  BEGIN
      DELETE TOP(100) FROM people
      WHERE id IN (
          SELECT id
          FROM (
              SELECT 
                  id,
                  ROW_NUMBER() OVER (
                      PARTITION BY name, id_card 
                      ORDER BY created_at ASC
                  ) AS record_rank
              FROM people
              WHERE created_at >= DATEADD(day, -2, GETDATE())
          ) AS duplicates
          WHERE record_rank > 1
      );
      WAITFOR DELAY '00:00:01'; -- Optional: Add a small delay to reduce load
  END
  

3. 关键注意事项 #

• Backup first: Always back up the people table (or export the records you're about to delete) before running deletes—better safe than sorry.
• Check for dependent data: Even though you mentioned old duplicates have关联 data, double-check that the recent duplicates don't have any existing dependencies:

  -- Replace `related_table` and `people_id` with your actual关联 table and foreign key
  SELECT * FROM related_table WHERE people_id IN (SELECT id FROM duplicate_candidates WHERE record_rank > 1);
  

  If there are dependencies, you'll need to decide whether to update those references first or adjust your deletion scope.
• Validate the rule: Make sure your PARTITION BY fields correctly identify duplicates—test with a small subset if you're unsure.

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