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中出现重复数据
防止重复数据从源头做起，比事后清理更有效。
-
唯一索引或约束： 在数据库表的设计阶段，为那些不应该重复的字段（例如，用户表中的email或username）创建唯一索引或唯一约束。这样，当尝试插入重复数据时，MySQL会报错，从而阻止重复数据的插入。
ALTER TABLE users ADD UNIQUE INDEX unique_email (email);
或者使用约束：
ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);
-
应用层校验： 在应用程序的代码中，在将数据插入数据库之前，先进行校验，检查是否存在重复数据。这可以在用户注册或数据导入等场景中进行。例如，在用户注册时，可以先查询数据库，检查是否存在相同的email地址。
-
使用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语句需要表中有一个主键或唯一索引，才能正常工作。
-
触发器： 可以使用触发器来在数据插入之前或之后进行校验，防止重复数据的插入。例如，可以创建一个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删除重复数据的性能
删除大量重复数据可能会影响数据库的性能，特别是当表非常大时。以下是一些优化删除重复数据性能的方法：
-
分批删除： 不要一次性删除所有的重复数据，而是分批删除。例如，每次删除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;
这种方法可以减少数据库的压力，避免长时间的锁定。
-
使用索引： 确保用于判断重复数据的字段上有索引。索引可以加快查询速度，从而提高删除重复数据的性能。
-
*避免使用`SELECT ：** 在子查询中，尽量只选择需要的字段，避免使用SELECT *`。这可以减少数据的传输量，提高查询速度。
-
优化sql语句： 仔细分析SQL语句，找出可以优化的地方。例如，可以使用EXPLAIN命令来分析SQL语句的执行计划，看看是否有可以改进的地方。
-
调整MySQL配置： 根据服务器的硬件配置和数据库的负载情况，调整MySQL的配置参数，例如innodb_buffer_pool_size、key_buffer_size等。
-
使用存储过程： 将删除重复数据的逻辑封装到存储过程中，可以减少客户端和服务器之间的通信次数，提高性能。
如何在删除重复数据时保留最新的一条记录
有时候，我们希望在删除重复数据时，保留最新的一条记录。这可以通过以下方法实现：
-
使用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中的记录。
-
使用窗口函数： 窗口函数也可以用来实现保留最新记录的功能。
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的记录。
-
使用临时表： 创建一个临时表，包含每个分组中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;
选择哪种方法取决于你的具体需求和数据量。对于小数据量，任何一种方法都可以。对于大数据量，建议使用分批删除、索引优化等方法来提高性能。
