MySQL删除重复行保留最小ID时触发最大行数错误求助

Got it, let's tackle this problem. The error you're hitting happens because your DELETE statement is trying to process way too many rows at once—MySQL's temporary tables or memory limits get overwhelmed when joining the table to itself to find duplicates. Here are a few solid fixes to get this done without hitting that max row error:

Instead of wiping all duplicates in one go, we can break the operation into smaller batches to avoid overwhelming your server. Here's a reusable loop for this:

REPEAT
  DELETE n1 FROM item_audit n1
  JOIN item_audit n2 
    ON n1.description = n2.description 
    AND n1.id > n2.id
  LIMIT 1000; -- Adjust this number based on your server's capacity
UNTIL ROW_COUNT() = 0 END REPEAT;

• The REPEAT...UNTIL loop will keep running the delete in chunks (here, 1000 rows at a time) until no more duplicates are found.
• Tweak the LIMIT value: if you still hit errors, lower it to 500 or 200; if your server can handle more, bump it up to 2000.

This approach first identifies the smallest ID for each description, then deletes all rows that don't match those "keep" IDs. It's often more efficient than the self-join for large datasets:

DELETE FROM item_audit
WHERE id NOT IN (
  SELECT min_id FROM (
    SELECT MIN(id) AS min_id
    FROM item_audit
    GROUP BY description
  ) AS keep_rows
);

• We need the nested subquery (keep_rows) because MySQL blocks direct references to the same table in a DELETE subquery.
• This cuts down on unnecessary comparisons—instead of joining every row to every duplicate, we only check against the minimal IDs for each description.

Before running any delete, adding a composite index on (description, id) can drastically speed up duplicate detection and reduce temporary table load:

CREATE INDEX idx_desc_id ON item_audit(description, id);

• This index helps MySQL quickly group rows by description and sort them by ID, so the delete operation doesn't have to scan the entire table from scratch every time.
• After the cleanup is done, you can keep the index to prevent future duplicate-related slowdowns, or drop it if it's not needed for other queries.

Quick Notes: #

• Always back up first! Run CREATE TABLE item_audit_backup AS SELECT * FROM item_audit; to make a safety copy before modifying data.
• If your table is massive, run these operations during off-peak hours to avoid disrupting other database traffic.

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