异常教程

MySQL 处理重复数据（建议收藏）

更新时间:

💡一则或许对你有用的小广告

欢迎加入小哈的星球 ，你将获得：专属的项目实战 / 1v1 提问 / Java 学习路线 / 学习打卡 / 每月赠书 / 社群讨论

  • 新项目:《从零手撸：仿小红书（微服务架构）》 正在持续爆肝中，基于 Spring Cloud Alibaba + Spring Boot 3.x + JDK 17...，点击查看项目介绍 ;
  • 《从零手撸：前后端分离博客项目（全栈开发）》 2 期已完结，演示链接： http://116.62.199.48/ ;

截止目前， 星球 内专栏累计输出 82w+ 字，讲解图 3441+ 张，还在持续爆肝中.. 后续还会上新更多项目，目标是将 Java 领域典型的项目都整一波，如秒杀系统, 在线商城, IM 即时通讯，权限管理，Spring Cloud Alibaba 微服务等等，已有 2900+ 小伙伴加入学习 ，欢迎点击围观

前言

在数据库管理中，重复数据（Duplicate Data）如同文件柜中混乱的纸张，不仅会占用存储空间，还可能引发逻辑错误或计算偏差。对于开发者而言，掌握如何高效处理 MySQL 中的重复数据，是提升数据质量与系统稳定性的关键技能。本文将从基础概念、操作方法到实际案例，系统性地讲解如何通过 MySQL 处理重复数据，并提供可直接复用的代码示例与策略建议。

一、理解重复数据：问题与影响

什么是重复数据？

重复数据指数据库表中存在两条或更多完全相同或部分相同的记录。例如，用户注册表中出现两个手机号、邮箱完全一致的用户信息，即为典型重复数据。

重复数据的潜在危害

  1. 存储浪费：重复记录占用额外存储空间，增加硬件成本。
  2. 逻辑错误：统计计算（如 SUM、COUNT）可能因重复数据导致结果偏差。
  3. 业务风险：例如电商系统中重复订单可能导致库存计算错误或重复发货。

比喻：将数据库比作图书馆的书架，重复数据如同同一本书被多次上架，不仅浪费空间，还让读者难以快速找到目标书籍。

二、检测重复数据的 3 种核心方法

方法 1：使用 DISTINCT 关键字筛选唯一值

DISTINCT 可快速过滤出唯一记录，适用于初步判断字段是否存在重复。

示例代码：

SELECT DISTINCT phone_number FROM users;  
-- 若结果数量少于总记录数，说明存在重复的电话号码

方法 2：通过 GROUP BY + HAVING 统计重复行

结合聚合函数（如 COUNT），可直接定位重复记录。

示例代码：

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

此查询会列出所有姓名和邮箱完全相同且出现次数超过 1 的记录。

方法 3：利用子查询定位重复主键（或唯一键）

若表包含主键（PRIMARY KEY）或唯一约束（UNIQUE），可通过子查询找出重复值。

示例代码：

SELECT *  
FROM orders  
WHERE order_id IN (  
    SELECT order_id  
    FROM orders  
    GROUP BY order_id  
    HAVING COUNT(*) > 1  
);

三、删除重复数据的 5 种策略

策略 1：保留最新记录并删除旧数据

使用 DELETE 结合子查询，根据时间戳字段筛选需保留的记录。

示例代码：

DELETE FROM users  
WHERE id NOT IN (  
    SELECT MAX(id)  
    FROM users  
    GROUP BY phone_number, email  
);

此语句会删除除每组重复数据中 id 最大的记录外的所有行。

策略 2：使用临时表重构数据

通过创建临时表存储唯一记录，再替换原表。

步骤示例：

-- 1. 创建临时表  
CREATE TABLE temp_users AS  
SELECT DISTINCT * FROM users;  

-- 2. 删除原表  
DROP TABLE users;  

-- 3. 重命名临时表  
ALTER TABLE temp_users RENAME TO users;

策略 3：基于行标识符的删除（如 ROW_NUMBER()）

MySQL 8.0+ 支持窗口函数，可更灵活地标记需删除的行。

示例代码：

DELETE  
FROM users  
WHERE id IN (  
    SELECT id  
    FROM (  
        SELECT id, ROW_NUMBER() OVER (PARTITION BY phone_number ORDER BY id) AS rn  
        FROM users  
    ) AS duplicates  
    WHERE rn > 1  
);

此方法通过 ROW_NUMBER() 为每组重复数据分配序号，删除序号大于 1 的行。

策略 4：事务控制确保数据一致性

在批量删除前开启事务，避免因中途错误导致数据混乱。

示例代码：

START TRANSACTION;  

DELETE FROM orders  
WHERE order_id IN (  
    SELECT order_id  
    FROM (  
        SELECT order_id, COUNT(*) AS cnt  
        FROM orders  
        GROUP BY order_id  
        HAVING cnt > 1  
    ) AS duplicates  
) AND id NOT IN (  
    SELECT MIN(id)  
    FROM orders  
    GROUP BY order_id  
);  

COMMIT;

策略 5：物理删除 vs 逻辑删除

若需保留历史记录，可添加 is_deleted 标志位实现逻辑删除，而非直接删除数据。

示例表结构：

ALTER TABLE users ADD COLUMN is_deleted BOOLEAN DEFAULT 0;

更新操作：

UPDATE users  
SET is_deleted = 1  
WHERE id NOT IN (  
    SELECT MIN(id)  
    FROM users  
    GROUP BY phone_number, email  
);

四、预防重复数据的 4 大措施

措施 1：设置唯一约束（UNIQUE）

通过定义唯一索引，强制字段组合的唯一性。

示例代码：

ALTER TABLE users  
ADD UNIQUE INDEX idx_unique_phone_email (phone_number, email);

措施 2：应用层验证

在代码逻辑中先查询数据库，确认数据是否已存在再执行插入操作。

伪代码示例：

def create_user(phone, email):  
    if db.exists(phone=phone, email=email):  
        return "该用户已存在"  
    else:  
        db.insert(phone=phone, email=email)

措施 3：使用触发器（Triggers）

通过数据库触发器，在插入或更新时自动检查重复数据。

示例触发器：

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

措施 4：设计合理的主键与索引

主键（如 AUTO_INCREMENT）可天然保证唯一性，合理设计索引可加速重复数据的检测效率。

示例索引：

CREATE INDEX idx_search ON users (phone_number, email);

五、实战案例：电商订单去重

案例背景

某电商平台发现 orders 表中存在重复订单，需清理数据并预防未来问题。

步骤 1：检测重复订单

SELECT order_id, COUNT(*)  
FROM orders  
GROUP BY order_id  
HAVING COUNT(*) > 1;

假设结果发现 order_id=1001 出现 3 次。

步骤 2：分析保留策略

决定保留 created_at 最新的记录。

步骤 3：执行删除操作

DELETE o1  
FROM orders o1  
INNER JOIN orders o2  
WHERE o1.order_id = o2.order_id  
AND o1.created_at < o2.created_at;

步骤 4：添加唯一约束

ALTER TABLE orders ADD UNIQUE (order_id);

步骤 5：应用层优化

在订单提交时，先通过 order_id 检查是否已存在。

结论

MySQL 处理重复数据是一个系统性工程，需结合检测、删除与预防三方面策略。本文通过代码示例与案例分析，展示了从基础查询到高级技巧的完整方法论。开发者应根据业务场景选择合适方案，例如对高并发系统可优先采用唯一约束，而历史数据清理则需谨慎使用事务与备份。通过持续优化数据治理流程，可显著提升数据库的健壮性与数据可靠性。

关键词自然布局：

  • MySQL 处理重复数据
  • 数据库重复数据检测
  • 删除重复数据策略
  • 唯一约束预防重复

（全文约 1800 字）

最新发布

查看更多文章