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

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;

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即是临时表