mysql删除重复数据只保留一条

原创已于 2022-05-09 12:21:35 修改 · 1.5w 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#Mysql 数据库

于 2016-04-29 08:28:07 首次发布

本文介绍了一种使用MySQL删除表中重复记录的方法，并提供了一个实际的例子。通过构建子查询结合分组统计来找出需要删除的重复项，最终实现保留一条记录的目标。

mysql删除重复数据只保留一条

新建一张测试表：

CREATE TABLE `book` (
  `id` char(32) NOT NULL DEFAULT '',
  `name` varchar(100) DEFAULT NULL,
  `parent_id` char(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

测试数据：

INSERT INTO `book` VALUES ('1', 'n1', '1');
INSERT INTO `book` VALUES ('2', 'n2', '1');
INSERT INTO `book` VALUES ('3', 'n3', '1');
INSERT INTO `book` VALUES ('4', 'n4', '3');
INSERT INTO `book` VALUES ('5', 'n5', '3');
INSERT INTO `book` VALUES ('6', 'n1', '1');
INSERT INTO `book` VALUES ('7', 'n2', '8');
INSERT INTO `book` VALUES ('8', 'n1', '5');
INSERT INTO `book` VALUES ('9', 'n4', '5');
INSERT INTO `book` VALUES ('10', 'n1', '5');
INSERT INTO `book` VALUES ('11', 'n2', '5');

查询删除重复数据前表中数据：

执行删除重复数据SQL：

DELETE book from book , 
(
   SELECT id FROM (
		SELECT id FROM book WHERE name IN(SELECT  name FROM book  GROUP BY name HAVING count(name) > 1) 
   ) t 
   WHERE id NOT IN (SELECT  id FROM book  GROUP BY name HAVING count(name) > 1)
) as a 
WHERE book.id = a.id;

删除重复数据后表中数据：