利用主键删除重复数据

 

CREATE TABLE `user1_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(20) DEFAULT NULL,
  `over` varchar(20) DEFAULT NULL,
  `mobile` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `user1_test` VALUES ('1', '唐僧', 'qujing', '12345678901,12345678902,12345678903');
INSERT INTO `user1_test` VALUES ('2', '猪八戒', 'qujing', '22345678901,22345678902,22345678903');
INSERT INTO `user1_test` VALUES ('3', '孙悟空', 'qujing', '32345678901,32345678902');
INSERT INTO `user1_test` VALUES ('4', '沙僧', 'qujing', '42345678901');
INSERT INTO `user1_test` VALUES ('8', '唐僧', 'qujing', '12345678901,12345678902,12345678903');
INSERT INTO `user1_test` VALUES ('9', '猪八戒', 'qujing', '22345678901,22345678902,22345678903');

 

  

MySQL-利用主键删除重复数据

1.查询数据是否重复

SELECT user_name,COUNT(*)
FROM user1_test
GROUP BY user_name 
HAVING COUNT(*)>1;

2.删除重复数据，对于相同数据保留ID最大的

SELECT a.*,b.* FROM user1_test a JOIN(
 SELECT user_name,COUNT(*),MAX(id) AS id
 FROM user1_test
 GROUP BY user_name HAVING COUNT(*)>1) b 
ON a.user_name=b.user_name
WHERE a.id<b.id;

 

DELETE a FROM user1_test a JOIN(
 SELECT user_name,COUNT(*),MAX(id) AS id
 FROM user1_test
 GROUP BY user_name HAVING COUNT(*)>1) b 
ON a.user_name=b.user_name
WHERE a.id<b.id;