使用SQL的GROUP BY和HAVING子句快速查找重复数据：SELECT column1, column2, COUNT(*) FROM table_name GROUP BY column1, column2 HAVING COUNT(*) > 1; 然后删除重复项：DELETE t1 FROM table_name t1 INNER JOIN table_name t2 WHERE t1.id > t2.id AND t1.column1 = t2.column1 AND t1.column2 = t2.column2;

查找重复数据

在同一张表中查找重复记录，可以使用以下SQL语句：select * from (select *,row_number() over(partition by name,phone order by id) as rn from user) t where t.rn > 1; 这个语句会找出所有重复的记录，按照name和phone分组。

高效去重方法

对于MySQL，使用自连接删除重复：DELETE a FROM table AS a,table AS b WHERE a.id > b.id AND a.name = b.name AND a.email = b.email; 这会保留id最小的记录，删除其他重复的。

窗口函数去重

使用ROW_NUMBER()窗口函数：WITH Ranked AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY id) AS rn FROM your_table) DELETE FROM Ranked WHERE rn > 1; 这在支持CTE的数据库如PostgreSQL或SQL Server中非常高效。

快速处理重复数据

先创建临时表备份：CREATE TEMPORARY TABLE temp_table AS SELECT * FROM original_table; 然后插入去重数据：INSERT INTO original_table (col1, col2, ...) SELECT DISTINCT col1, col2, ... FROM temp_table; 最后清空并替换。

使用索引优化

在可能重复的列上添加索引：ALTER TABLE table_name ADD INDEX idx_dup (col1, col2); 这样GROUP BY查询会更快，减少全表扫描时间。

批量删除脚本

对于大量数据，分批删除：WHILE EXISTS (SELECT 1 FROM table_name GROUP BY col1, col2 HAVING COUNT(*) > 1 LIMIT 1000) DO DELETE t1 FROM table_name t1 INNER JOIN (SELECT col1, col2, MIN(id) as min_id FROM table_name GROUP BY col1, col2 HAVING COUNT(*) > 1) t2 ON t1.col1 = t2.col1 AND t1.col2 = t2.col2 AND t1.id > t2.min_id LIMIT 1000; END WHILE;

FAQ
Q: 如何避免未来插入重复数据？
A: 在表上添加唯一索引：ALTER TABLE table_name ADD UNIQUE INDEX idx_unique (col1, col2);
Q: MySQL不支持CTE怎么办？
A: 用子查询模拟：DELETE FROM table_name WHERE id NOT IN (SELECT * FROM (SELECT MIN(id) FROM table_name GROUP BY col1, col2) AS temp);
Q: 去重后数据丢失怎么办？
A: 先备份表：CREATE TABLE backup AS SELECT * FROM table_name;
Q: Oracle数据库怎么去重？
A: DELETE FROM table_name WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM table_name GROUP BY col1, col2);
Q: 性能最优的方法是什么？
A: 结合索引和窗口函数，对于大数据集最快。