mysql添加唯一索引的时候有重复数据

场景

有这样一种情况
在创建表的时候忘记添加唯一键
发现的时候已经有重复数据
对已有重复数据的处理方案是，留下最先创建那一条数据

1.创建表，并插入数据，还原问题现场

DROP TABLE IF EXISTS `test`;

CREATE TABLE `test` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT  COMMENT '主键id',
  `name` VARCHAR(10) NOT NULL COMMENT 'name',
  PRIMARY KEY (`id`) 
) COMMENT='test';

INSERT INTO test(`name`) values
('张三'),
('张三'),
('李四'),
('李四'),
('李四'),
('李四'),
('王五');

2.查看添加数据

SELECT * FROM test;

1.jpg

3.直接添加唯一索引

ALTER TABLE `test` ADD UNIQUE(`name`);

2.jpg

4.分组查看重复数据重复次数

SELECT `name`, COUNT(id) FROM test GROUP BY `name`;

3.jpg

5.找出重复数据的id,并且查出要保留的id

SELECT `name`, COUNT(id), GROUP_CONCAT(id), MIN(id) FROM test GROUP BY `name` HAVING COUNT(id) > 1;

4.jpg

这里的GROUP_CONCAT(id)就是重复的id，MIN(id)数据就是要保留的数据

6.过滤出需要删除数据id

SELECT `name`, COUNT(id), replace(GROUP_CONCAT(id), min(id), '') id FROM test GROUP BY `name` HAVING COUNT(id) > 1;

5.jpg

这里id的数据就是我们需要删除的数据了

7.把需要删除的id整合到一列

SELECT GROUP_CONCAT(id) FROM (
SELECT `name`, COUNT(id), replace(GROUP_CONCAT(id), min(id), '') id
FROM test GROUP BY `name` HAVING COUNT(id) > 1) t

6.jpg

8.完整的删除SQL

DELETE FROM test WHERE  FIND_IN_SET(id, (
SELECT GROUP_CONCAT(id) FROM (
SELECT `name`, COUNT(id), replace(GROUP_CONCAT(id), min(id), '') id
FROM test GROUP BY `name` HAVING COUNT(id) > 1 ) t))

7.jpg
8.jpg

9.添加唯一索引

ALTER TABLE `test` ADD UNIQUE(`name`);

9.jpg