删除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);

 原始数据:

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

三种方式

目标:删除重复数据,保留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 左右

 

结论

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

上一篇:【游记】CSP-J/S 2021 游记


下一篇:2021 ICPC 江西省大学生程序设计竞赛 J 二分 队列 模拟