mysql删除重复数据，保留一条记录

谁践踏了优雅 2022-01-31 16:45 272阅读 0赞

# 1、数据准备 #

创建表

CREATE TABLE `user`  (
      `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
      `age` int(11) NOT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 21 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

插入数据

INSERT INTO `user` VALUES (1, 1);
    INSERT INTO `user` VALUES (2, 2);
    INSERT INTO `user` VALUES (3, 3);
    INSERT INTO `user` VALUES (10, 5);
    INSERT INTO `user` VALUES (11, 6);
    INSERT INTO `user` VALUES (12, 7);
    INSERT INTO `user` VALUES (13, 8);
    INSERT INTO `user` VALUES (14, 9);
    INSERT INTO `user` VALUES (15, 1);
    INSERT INTO `user` VALUES (16, 2);
    INSERT INTO `user` VALUES (17, 3);
    INSERT INTO `user` VALUES (18, 1);
    INSERT INTO `user` VALUES (19, 1);
    INSERT INTO `user` VALUES (20, 3);

# 2、查询重复数据 #

select age,count(age) from user GROUP BY age HAVING count(age)>1;

![2019051708475993.png][]

## 2.1 查询表中多余数据（保留min(id)） ##

第一种方式

select * from user u1 where u1.age in (select u2.age from user u2 GROUP BY u2.age HAVING count(u2.age)>1) and u1.id not in (select min(u3.id) from user u3 GROUP BY u3.age HAVING count(u3.age)>1);

第二种方式

select * from user where id not in(select min(id) from user group by age);

推荐使用第二种方式

# 3、删除多余数据 #

>delete from user where id not in(select min(id) from user group by age);
    1093 - You can't specify target table 'user' for update in FROM clause

直接使用上面的sql，会报错。原因是：更新这个表的同时又查询了这个表，查询这个表的同时又去更新了这个表，可以理解为死锁。mysql不支持这种更新查询同一张表的操作。

解决办法：把要更新的几列数据查询出来做为一个第三方表，然后筛选更新。

delete from user where id in (select t.id from (select id from user where id not in (select min(id) from user group by age)) t );

t即是临时表

[2019051708475993.png]: /images/20220131/5bbd47b8ed724c708bb3de482d5ebaa2.png