如何用MySQL删除所有重复条目（不保留任一行）

Got it, let's sort this out for you. You need to remove every row that’s part of a duplicate group—meaning if an RFID value appears more than once, all rows with that RFID get deleted, leaving only rows with completely unique RFID values. Your current SQL statement won’t work because MySQL doesn’t support that syntax for DELETE, and the common "keep max/min ID" trick isn’t what you’re after here.

Why Your Current Query Fails #

The statement DELETE * ,count(*)as n FROM cart by rfid HAVING n>1 is invalid because:

• You can’t include aggregate functions like COUNT(*) directly in a DELETE clause
• The GROUP BY syntax here isn’t compatible with MySQL’s DELETE operation rules

Solution 1: Subquery to Target Duplicate RFIDs #

This method first identifies all RFID values that have duplicates, then deletes every row linked to those RFIDs:

DELETE FROM cart
WHERE RFID IN (
    SELECT RFID
    FROM cart
    GROUP BY RFID
    HAVING COUNT(*) > 1
);

Solution 2: JOIN (Better for Large Tables) #

If your table has a lot of data, a JOIN-based approach often performs better than an IN subquery. It achieves the exact same result:

DELETE c
FROM cart c
JOIN (
    SELECT RFID
    FROM cart
    GROUP BY RFID
    HAVING COUNT(*) > 1
) dup ON c.RFID = dup.RFID;

Always Verify Before Deleting! #

Never run a DELETE blind—preview which rows will be removed first by swapping DELETE with SELECT *:

SELECT * FROM cart
WHERE RFID IN (
    SELECT RFID
    FROM cart
    GROUP BY RFID
    HAVING COUNT(*) > 1
);

For your sample data, this will return the two rows with RFID=1—exactly the ones you want to erase. After running the DELETE, only rows with RFID=2 and RFID=3 will stay.

Quick Tips #

• Backup first: Always create a backup or run the DELETE in an InnoDB transaction so you can roll back if something goes wrong.
• Adjust for multi-column duplicates: If duplicates are defined by more than one column (e.g., RFID + CATAGORY), just add those columns to the GROUP BY clause in the subquery.

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