详细分析Mysql去重的几种方法（附Demo）

前言

在MySQL中，SELECT DISTINCT 和 GROUP BY 可以用来去除重复记录，二者有相似的功能，但在某些情况下有所不同

1. DISTINCT

SELECT DISTINCT 用于从表中选择唯一的记录，去除所有重复的数据行

直接作用于结果集，并去除所有指定列上的重复值

SELECT DISTINCT equipment_no
FROM equipment_check_order;

在单列去重的场景下，效率较高。但是在处理大数据集时，可能会影响性能，因为需要全表扫描和去重

2. GROUP BY

GROUP BY 用于将具有相同值的记录分组，并可以进行聚合操作

即使不使用聚合函数，仅使用 GROUP BY 也可以达到去重的效果

SELECT equipment_no
FROM equipment_check_order
GROUP BY equipment_no;

3. Mysql版本

3.1 低版本

在不支持窗口函数的 MySQL 版本中，可以使用子查询来进行去重

假设希望去除 equipment_check_order 表中的重复 equipment_no，并保留每个 equipment_no 的最新记录，可以使用子查询的方式来实现

-- 创建目标表
CREATE TABLE IF NOT EXISTS deduplicated_orders (
    equipment_no VARCHAR(255),
    check_date DATE,
    PRIMARY KEY (equipment_no, check_date)
);

-- 将去重后的数据插入到目标表
INSERT INTO deduplicated_orders (equipment_no, check_date)
SELECT equipment_no, check_date
FROM equipment_check_order AS e
WHERE check_date = (
    SELECT MAX(check_date)
    FROM equipment_check_order
    WHERE equipment_no = e.equipment_no
);

或者使用自联拼接的方式:

-- 创建目标表
CREATE TABLE IF NOT EXISTS deduplicated_orders (
    equipment_no VARCHAR(255),
    check_date DATE,
    PRIMARY KEY (equipment_no, check_date)
);

-- 使用自联接将去重后的数据插入到目标表
INSERT INTO deduplicated_orders (equipment_no, check_date)
SELECT e1.equipment_no, e1.check_date
FROM equipment_check_order e1
LEFT JOIN equipment_check_order e2
ON e1.equipment_no = e2.equipment_no
AND e1.check_date < e2.check_date
WHERE e2.check_date IS NULL;

3.2 高版本

使用 ROW_NUMBER() 和 WITH 子句进行去重

基本步骤：

• 定义公共表表达式（CTE）： 使用 WITH 子句创建一个临时结果集
• 应用 ROW_NUMBER() 函数： 在 CTE 内部为每一行分配唯一的行号
• 选择需要的记录： 在外层查询中，根据行号筛选出需要的记录

示例：保留每个 equipment_no 的最新记录
假设有一个表 equipment_check_order，包含 equipment_no 和 check_date 列，希望去除重复的 equipment_no，并保留每个 equipment_no 的最新记录（即 check_date 最大的记录）

-- 创建目标表（如果不存在）
CREATE TABLE IF NOT EXISTS deduplicated_orders (
    equipment_no VARCHAR(255),
    check_date DATE,
    PRIMARY KEY (equipment_no, check_date)
);

-- 使用 CTE 和 ROW_NUMBER() 进行去重
WITH ranked AS (
  SELECT equipment_no,
         check_date,
         ROW_NUMBER() OVER (PARTITION BY equipment_no ORDER BY check_date DESC) AS rn
  FROM equipment_check_order
)
INSERT INTO deduplicated_orders (equipment_no, check_date)
SELECT equipment_no, check_date
FROM ranked
WHERE rn = 1;

• PARTITION BY equipment_no：根据 equipment_no 列进行分组
• SELECT equipment_no, check_date FROM ranked WHERE rn = 1：从 CTE ranked 中选择行号为 1 的记录，即每个分组中最新的记录，如果为小于5，代表去重并保留多条记录

4. 总结

方法	用法示例	优点	缺点	适用场景
SELECT DISTINCT	SELECT DISTINCT equipment_no FROM equipment_check_order;	简单易用，直观	对大数据集性能影响可能较大	单列去重，简单查询
GROUP BY	SELECT equipment_no FROM equipment_check_order GROUP BY equipment_no;	灵活，与聚合函数结合使用方便	语法复杂，对性能影响与数据量有关	复杂查询或需要与聚合函数结合使用的场景
子查询 + ROW_NUMBER()		高灵活性，可选择特定记录	需使用窗口函数，语法复杂	高级去重，保留特定记录
临时表		适合复杂数据处理，多步骤数据操作	操作步骤多，占用额外存储空间	数据清理、迁移，复杂操作