SQL删除重复数据：移除data库disk表中mag列重复的整行记录

Let's break down how to solve this based on what exactly you need—since "删除所有mag列值重复的整行记录" could mean two slightly different scenarios, I'll cover both common use cases:

Scenario 1: Delete all rows where mag has duplicates (remove every instance of duplicated mag) #

If you want to completely remove any mag value that appears more than once (so none of the rows with that mag stay in the table), use this query:

-- Delete all rows with duplicated mag values
DELETE FROM disk
WHERE mag IN (
    SELECT mag
    FROM disk
    GROUP BY mag
    HAVING COUNT(*) > 1
);

How this works: #

• The subquery first identifies all mag values that occur more than once in the table.
• The outer DELETE statement removes every row where the mag is in that list of duplicated values.

Scenario 2: Keep one unique row per mag, delete the rest (common use case) #

More often, you'll want to retain one instance of each mag (e.g., the first one added, identified by the smallest id) and delete the duplicates. Here's how to do that:

-- Delete duplicate rows, retain the row with the smallest id for each mag
DELETE d1
FROM disk d1
INNER JOIN disk d2 
    ON d1.mag = d2.mag 
    AND d1.id > d2.id;

How this works: #

• We perform a self-join on the disk table, matching rows with the same mag value.
• We target rows where d1.id is larger than d2.id—this ensures we only delete the "duplicate" rows, leaving the one with the smallest id (the earliest entry, assuming id is auto-incrementing).

Critical Notes Before You Run These Queries: #

• Always back up your data first! Run a SELECT query to verify which rows will be deleted before executing the DELETE. For example, for Scenario 1:

  SELECT * FROM disk WHERE mag IN (SELECT mag FROM disk GROUP BY mag HAVING COUNT(*) > 1);
  

• If your id column isn't set as the primary key, consider adding that constraint first to ensure row uniqueness—it'll make future data management easier.

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