MySQL删除重复数据

创建测试用例

创建test表

CREATE TABLE test(
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `price` decimal(8, 2) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

表中插入数据

INSERT INTO test (`id`, `name`, `price`) VALUES ('1', '苹果', 10);
INSERT INTO test (`id`, `name`, `price`) VALUES ('2', '橘子', 11);
INSERT INTO test (`id`, `name`, `price`) VALUES ('3', '葡萄', 12);
INSERT INTO test (`id`, `name`, `price`) VALUES ('4', '橘子', 11);
INSERT INTO test (`id`, `name`, `price`) VALUES ('5', '葡萄', 12);

测试

查看name重复的数据

select name from test group by name having count(0) > 1;

删除全部重复的数据（全部重复的数据）

delete from test where name in (select t.name from (select name from test group by name having count(1) > 1 )t );

查看要删除的数据（保留id最小的数据）

select * from test where id not in (select t.id from (select min(id) as id from test group by name)t);

删除重复数据（保留id最小的数据）

delete from test where id not in (select t.id from (select min(id) as id from test group by name)t)