MySQL 中删除重复的数据并只保留一条

时间：2025-02-17 22:09:37

要在 MySQL 中删除重复的数据并只保留一条，可以使用下面的方法。

方法一：使用 left join (推荐方法删除重复数据，添加唯一组合索引，可以使用，数据量大的也可以)

// 先把历史数据删除，才能够添加唯一的组合索引
DELETE u1
FROM uf_cs_record_batch_detail u1
LEFT JOIN (
    SELECT MIN(id) AS min_id
    FROM uf_cs_record_batch_detail
    GROUP BY cs_contact_name, cs_safe_remark
) u2
ON u1.id = u2.min_id
WHERE u2.min_id IS NULL;

// 添加组合的唯一索引
ALTER TABLE uf_cs_record_batch_detail ADD UNIQUE KEY idx_uni_contact_safe_stat (cs_contact_name, cs_safe_remark);

方法二：创建临时表(需要分开多步执行，逻辑清晰，但是会改变ID值)

这种方法假设你有一个表 your_table，并且你要基于某些列来判断哪些数据是重复的。例如，如果你想删除基于 column1 和 column2 的重复记录，只保留一条记录，你可以按照以下步骤操作：

  1. 使用 CREATE TABLE 语句创建一个临时表，用于存储唯一的记录。
  2. 使用 INSERT INTO ... SELECT 语句将唯一的记录插入到临时表中。
  3. 删除原始表中的所有记录。
  4. 使用 INSERT INTO ... SELECT 语句将临时表中的记录插入回原始表。
  5. 删除临时表。

以下是一个完整的 SQL 例子：

-- Step 1: Create a temporary table
CREATE TABLE temp_table AS
SELECT * FROM your_table
WHERE 1=0;  -- Create an empty table with the same structure as your_table

-- Step 2: Insert unique records into the temporary table
INSERT INTO temp_table
SELECT * FROM your_table t1
WHERE t1.id = (
    SELECT MIN(t2.id)
    FROM your_table t2
    WHERE t1.column1 = t2.column1
    AND t1.column2 = t2.column2
);

-- Step 3: Delete all records from the original table
DELETE FROM your_table;

-- Step 4: Insert the records back from the temporary table to the original table
INSERT INTO your_table
SELECT * FROM temp_table;

-- Step 5: Drop the temporary table
DROP TABLE temp_table;

解释：

  1. 创建临时表：CREATE TABLE temp_table AS SELECT * FROM your_table WHERE 1=0; 这行代码创建了一个空的临时表，其结构与 your_table 相同。
  2. 插入唯一记录：INSERT INTO temp_table SELECT * FROM your_table t1 WHERE = (SELECT MIN() FROM your_table t2 WHERE t1.column1 = t2.column1 AND t1.column2 = t2.column2); 这行代码将唯一记录插入到临时表中，基于 column1 和 column2 进行去重，只保留 id 最小的记录。
  3. 删除原始表中的所有记录：DELETE FROM your_table; 删除原始表中的所有记录。
  4. 将临时表中的记录插入回原始表：INSERT INTO your_table SELECT * FROM temp_table; 将临时表中的记录插入回原始表。
  5. 删除临时表：DROP TABLE temp_table; 删除临时表。

这样，你就成功地删除了原始表中的重复记录，只保留了一条唯一记录。

方法三：使用 JOIN 自连查询(需要注意性能问题)

上面的这个方案不太行，会改变原来的数据id

为了避免改变原来的数据 ID，我们可以使用一个不同的方法，通过使用自连接来标记重复的数据并删除多余的记录。这种方法在保留原始 ID 的情况下删除重复记录。

假设你的表结构如下：

  • 表名：your_table
  • 列名：id (主键), column1, column2, 以及其他列。

你可以使用以下 SQL 语句来删除重复记录，只保留一条（通常是保留 ID 最小的那一条）：

-- Step 1: 标记要删除的重复记录
DELETE t1
FROM your_table t1
INNER JOIN your_table t2 
WHERE 
    t1.id > t2.id
    AND t1.column1 = t2.column1
    AND t1.column2 = t2.column2;

-- Step 2: 确认删除成功，查看剩余数据
SELECT * FROM your_table;

解释：

  1. 标记要删除的重复记录：我们使用自连接 INNER JOIN 来找到重复的记录，并且使用 WHERE > 来确保只删除 id 较大的记录，从而保留 id 最小的记录。
  2. 确认删除成功：通过 SELECT 语句查看剩余的数据，确保删除操作正确。

这个方法的优点是：

  • 不会改变原始数据的 ID。
  • 保留每组重复记录中 ID 最小的一条记录。
  • 操作简单且高效。

总结

  • 创建临时表 适合需要重建数据表的场景，适合数据量中等的情况。
  • 自连查询 能保留最小 ID，适合不想改变 ID 的情况下删除重复数据。
  • 使用子查询 是一种简单高效的方法，适合数据量适中且保留最小 ID 的需求。
  • 根据实际项目的需求选择合适的方法，确保在删除数据时不会破坏现有的逻辑和关联。

相关文章