MySQL查询和删除重复记录的操作方法

查询重复记录

1. 基本查询方法

SELECT column_name, COUNT(*) 
FROM table_name 
GROUP BY column_name 
HAVING COUNT(*) > 1;

2. 查询多列组合重复

SELECT column1, column2, COUNT(*) 
FROM table_name 
GROUP BY column1, column2 
HAVING COUNT(*) > 1;

3. 查看完整重复记录

SELECT * FROM table_name 
WHERE column_name IN (
    SELECT column_name 
    FROM table_name 
    GROUP BY column_name 
    HAVING COUNT(*) > 1
) 
ORDER BY column_name;

删除重复记录

1. 使用临时表方法

-- 创建临时表存储不重复记录
CREATE TABLE temp_table AS
SELECT DISTINCT * FROM original_table;

-- 清空原表
TRUNCATE TABLE original_table;

-- 将不重复记录插回原表
INSERT INTO original_table SELECT * FROM temp_table;

-- 删除临时表
DROP TABLE temp_table;

2. 使用DELETE JOIN方法（保留一条记录）

DELETE t1 FROM table_name t1
INNER JOIN table_name t2 
WHERE 
    t1.id < t2.id AND  -- 假设有id列，保留id较大的记录
    t1.column_name = t2.column_name;  -- 重复的列

3. 使用ROW_NUMBER()窗口函数（MySQL 8.0+）

-- 先查询确认要删除的记录
SELECT * FROM (
    SELECT *, 
    ROW_NUMBER() OVER (PARTITION BY column_name ORDER BY id) AS row_num
    FROM table_name
) t WHERE row_num > 1;

-- 实际删除操作
DELETE FROM table_name 
WHERE id IN (
    SELECT id FROM (
        SELECT id, 
        ROW_NUMBER() OVER (PARTITION BY column_name ORDER BY id) AS row_num
        FROM table_name
    ) t WHERE row_num > 1
);

注意事项

1. 在执行删除操作前，务必先备份数据
2. 对于大型表，删除操作可能会很耗时，考虑在低峰期执行
3. 可以使用事务来确保操作的安全性： sql START TRANSACTION; -- 执行删除操作 -- 确认无误后 COMMIT; -- 或者有问题时 ROLLBACK;
4. 对于没有唯一标识列的表，删除操作会更加复杂，可能需要创建临时标识列

最佳实践

1. 在应用层防止重复数据插入
2. 在数据库表设计时添加唯一约束
3. 定期检查并清理重复数据