删除MySQL数据库重复数据保留一条

 在开发中，遇到了多次需要删除重复数据并且根据条件保留一条的情况，因此就做个总结。

以此表为例：

-- 用户表 
create table t_user
(
    id        bigint auto_increment
        primary key,
    name      varchar(20) not null,
    password  varchar(20) not null,
    lessee_id int         not null
)
    comment '用户表';
create index idx_name
    on t_user (name);

 原始数据：

三种方式

目标：删除重复数据，保留ID最大的一条

-- 第一种
DELETE t1
FROM t_user t1,
     t_user t2
WHERE t1.lessee_id = t2.lessee_id
  AND t1.name = t2.name
  AND t1.id < t2.id;

-- 第二种
delete
from t_user
where id not in (
    (select t1.max_id
     from (select max(id) as max_id from t_user group by name, lessee_id having count(1) > 1) t1))
  and (lessee_id, name) in
      (select t2.lessee_id, t2.name
       from (select lessee_id, name from t_user group by name, lessee_id having count(1) > 1) t2);

-- 第三种
delete
from t_user
where id not in (select * from (select max(id) from t_user group by name, lessee_id) t2);

效率对比

往表中插入测试数据：

DELIMITER $$
CREATE PROCEDURE pro_copy()
BEGIN
SET @i=1; -- 起始
WHILE @i<=500000 DO
INSERT INTO t_user(NAME,password,lessee_id) VALUES(CONCAT('user',@i),'123',5); -- 拼接USER 和i值
SET @i=@i+1; -- 防止成为死循环
END WHILE; -- 结束循环
END $$ -- 结束自定义结束符
DELIMITER ;
call pro_copy(); -- 调用函数

方式	20w数据去重耗时（name无索引）	50w数据去重耗时（name有索引）
第一种	3min以上	3s 523ms 左右
第二种	6s 172ms 左右	16s 726ms 左右
第三种	3s 868ms 左右	13s 656ms 左右

 

结论

在查询条件有索引的情况下，选择第一种去重方式，没有索引的情况下选择第三种。