MySQL去重不求人！4种方法教你轻松查找&删除重复数据

还在为MySQL数据库中重复数据烦恼？本文为你提供一套MySQL去重实操教学方案，助你快速查找并删除重复记录，提升数据质量。文章详细介绍了四种高效的去重方法：包括利用GROUP BY和HAVING子句、ROW_NUMBER()窗口函数、创建临时表以及结合DISTINCT关键字。同时，还分享了避免重复数据产生的实用技巧，如设置唯一索引、应用层校验等。针对大数据量场景，提供了分批删除、索引优化等性能优化策略。无论你是数据库新手还是经验丰富的开发者，都能从中找到适合自己的解决方案，轻松应对MySQL数据去重挑战。

MySQL处理重复数据的方法有四种：方案一利用GROUP BY和HAVING子句查找并删除重复记录，适合常规场景；方案二使用ROW_NUMBER()窗口函数分配行号后删除重复项，适用于MySQL 8.0及以上版本；方案三通过创建临时表保留唯一记录再导回原表，适合大数据量操作；方案四结合DISTINCT关键字与临时表删除完全重复记录。此外，为避免重复数据可设置唯一索引、应用层校验、使用INSERT IGNORE或REPLACE语句及触发器。优化删除性能可通过分批删除、使用索引、避免SELECT *、优化SQL语句、调整配置参数及封装存储过程实现。如需保留最新记录，可使用MAX()函数、窗口函数按ID降序排列或临时表方式完成。

MySQL处理重复数据，通常涉及到查找和删除两个环节。核心目标是识别哪些数据是重复的，然后决定如何处理这些重复项，是保留一份删除其余，还是全部删除，或者进行合并操作，这取决于具体的业务需求。

查找与删除重复记录的4种方案：

方案一：利用GROUP BY和HAVING子句查找重复数据

这是最常用的方法之一。GROUP BY可以将具有相同字段值的记录分组，然后HAVING子句可以过滤出那些计数大于1的组，即重复的记录。

例如，假设我们有一个名为users的表，其中包含id和email字段，我们想找出所有具有相同email地址的用户。

SELECT email, COUNT(*) AS count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

这个查询会返回所有重复的email地址以及它们出现的次数。

接下来，如果你想删除这些重复的记录，你可以使用一个子查询来找到这些重复的email地址，然后删除users表中具有这些email地址的记录。但是，直接删除可能会导致问题，因为你可能不想删除所有的重复记录。通常，你会想保留其中一条记录。

方案二：使用ROW_NUMBER()窗口函数

MySQL 8.0引入了窗口函数，这使得查找和删除重复数据变得更加容易。ROW_NUMBER()函数可以为每个分组内的记录分配一个唯一的行号。我们可以使用这个行号来识别并删除重复的记录。

WITH RowNumCTE AS (
    SELECT
        id,
        email,
        ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS RowNum
    FROM
        users
)
SELECT * FROM RowNumCTE WHERE RowNum > 1;

DELETE FROM users
WHERE id IN (SELECT id FROM RowNumCTE WHERE RowNum > 1);

这个查询首先使用ROW_NUMBER()函数为每个具有相同email地址的用户分配一个行号，然后选择所有行号大于1的记录，这些就是重复的记录。最后，删除这些重复的记录。注意，这里假设id字段是唯一的，并且可以用来标识每一条记录。

方案三：创建临时表

创建临时表是一种比较安全且常用的方法，尤其是当数据量较大时。这个方法的核心思想是先将不重复的数据插入到临时表中，然后清空原表，最后将临时表的数据导回原表。

CREATE TEMPORARY TABLE temp_users AS
SELECT MIN(id) AS id, email
FROM users
GROUP BY email;

TRUNCATE TABLE users;

INSERT INTO users (id, email)
SELECT id, email
FROM temp_users;

DROP TEMPORARY TABLE IF EXISTS temp_users;

这个方法首先创建一个名为temp_users的临时表，并将所有不重复的email地址插入到这个临时表中。然后，清空users表，并将temp_users表中的数据导回users表。最后，删除临时表。这种方法可以确保只保留每个email地址的第一条记录。

方案四：使用DISTINCT关键字

虽然DISTINCT关键字主要用于查询不重复的记录，但它也可以与INSERT INTO ... SELECT语句结合使用来删除重复数据。

CREATE TABLE temp_users AS
SELECT DISTINCT * FROM users;

TRUNCATE TABLE users;

INSERT INTO users SELECT * FROM temp_users;

DROP TABLE temp_users;

这个方法创建一个临时表temp_users，包含users表中所有不同的记录。然后，清空users表，并将temp_users表中的数据导回users表。最后，删除临时表。这种方法会删除所有完全重复的记录，即所有字段的值都相同的记录。

如何避免MySQL中出现重复数据

防止重复数据从源头做起，比事后清理更有效。

1. 唯一索引或约束： 在数据库表的设计阶段，为那些不应该重复的字段（例如，用户表中的email或username）创建唯一索引或唯一约束。这样，当尝试插入重复数据时，MySQL会报错，从而阻止重复数据的插入。

   ALTER TABLE users ADD UNIQUE INDEX unique_email (email);

   或者使用约束：

   ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);

2. 应用层校验： 在应用程序的代码中，在将数据插入数据库之前，先进行校验，检查是否存在重复数据。这可以在用户注册或数据导入等场景中进行。例如，在用户注册时，可以先查询数据库，检查是否存在相同的email地址。

3. 使用INSERT IGNORE或REPLACE语句： 如果你无法避免重复数据的插入，可以使用INSERT IGNORE或REPLACE语句来处理。INSERT IGNORE语句会忽略插入重复数据的操作，而REPLACE语句会先删除表中已存在的重复数据，然后再插入新数据。

   INSERT IGNORE INTO users (email, ...) VALUES ('test@example.com', ...);
   
   REPLACE INTO users (email, ...) VALUES ('test@example.com', ...);

   需要注意的是，REPLACE语句需要表中有一个主键或唯一索引，才能正常工作。

4. 触发器： 可以使用触发器来在数据插入之前或之后进行校验，防止重复数据的插入。例如，可以创建一个BEFORE INSERT触发器，在每次插入数据之前，先检查是否存在重复数据，如果存在，则阻止插入操作。

   CREATE TRIGGER prevent_duplicate_email
   BEFORE INSERT ON users
   FOR EACH ROW
   BEGIN
       IF EXISTS (SELECT 1 FROM users WHERE email = NEW.email) THEN
           SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Duplicate email address';
       END IF;
   END;

   这个触发器会在每次向users表插入数据之前，检查是否存在相同的email地址。如果存在，则会抛出一个错误，阻止插入操作。

如何优化MySQL删除重复数据的性能

删除大量重复数据可能会影响数据库的性能，特别是当表非常大时。以下是一些优化删除重复数据性能的方法：

1. 分批删除： 不要一次性删除所有的重复数据，而是分批删除。例如，每次删除1000条重复记录，然后暂停一段时间，让数据库有时间处理其他任务。

   -- 循环删除，每次删除1000条
   WHILE TRUE DO
       DELETE FROM users
       WHERE id IN (SELECT id FROM (SELECT id FROM users WHERE ... /* 重复数据判断条件 */ LIMIT 1000) AS tmp);
   
       -- 检查是否还有重复数据，如果没有则退出循环
       IF ROW_COUNT() = 0 THEN
           LEAVE;
       END IF;
   
       -- 暂停一段时间，例如1秒
       DO SLEEP(1);
   END WHILE;

   这种方法可以减少数据库的压力，避免长时间的锁定。

2. 使用索引： 确保用于判断重复数据的字段上有索引。索引可以加快查询速度，从而提高删除重复数据的性能。

3. *避免使用`SELECT ：** 在子查询中，尽量只选择需要的字段，避免使用SELECT *`。这可以减少数据的传输量，提高查询速度。

4. 优化SQL语句： 仔细分析SQL语句，找出可以优化的地方。例如，可以使用EXPLAIN命令来分析SQL语句的执行计划，看看是否有可以改进的地方。

5. 调整MySQL配置： 根据服务器的硬件配置和数据库的负载情况，调整MySQL的配置参数，例如innodb_buffer_pool_size、key_buffer_size等。

6. 使用存储过程： 将删除重复数据的逻辑封装到存储过程中，可以减少客户端和服务器之间的通信次数，提高性能。

如何在删除重复数据时保留最新的一条记录

有时候，我们希望在删除重复数据时，保留最新的一条记录。这可以通过以下方法实现：

1. 使用MAX()函数和GROUP BY子句： 找到每个分组中id最大的记录，然后删除其他记录。

   DELETE FROM users
   WHERE id NOT IN (SELECT id FROM (SELECT MAX(id) AS id FROM users GROUP BY email) AS tmp);

   这个查询首先使用MAX()函数和GROUP BY子句找到每个email地址对应的最大id，然后删除users表中id不在这些最大id中的记录。

2. 使用窗口函数： 窗口函数也可以用来实现保留最新记录的功能。

   WITH RowNumCTE AS (
       SELECT
           id,
           email,
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY id DESC) AS RowNum
       FROM
           users
   )
   DELETE FROM users
   WHERE id IN (SELECT id FROM RowNumCTE WHERE RowNum > 1);

   这个查询首先使用ROW_NUMBER()函数为每个具有相同email地址的用户分配一个行号，按照id降序排列，然后删除所有行号大于1的记录。

3. 使用临时表： 创建一个临时表，包含每个分组中id最大的记录，然后清空原表，并将临时表的数据导回原表。

   CREATE TEMPORARY TABLE temp_users AS
   SELECT MAX(id) AS id, email
   FROM users
   GROUP BY email;
   
   TRUNCATE TABLE users;
   
   INSERT INTO users (id, email)
   SELECT id, email
   FROM temp_users;
   
   DROP TEMPORARY TABLE IF EXISTS temp_users;

选择哪种方法取决于你的具体需求和数据量。对于小数据量，任何一种方法都可以。对于大数据量，建议使用分批删除、索引优化等方法来提高性能。

今天带大家了解了的相关知识，希望对你有所帮助；关于数据库的技术知识我们会一点点深入介绍，欢迎大家关注golang学习网公众号，一起学习编程~