删除MySQL表中的重复数据？

原创

青衫染红尘

发布于 2023-02-28 17:36:36

7.2K0

发布于 2023-02-28 17:36:36

文章被收录于专栏：Surpass' Blog

前言

一般我们将数据存储在MySQL数据库中，它允许我们存储重复的数据。但是往往重复的数据是作废的、没有用的数据，那么通常我们会使用数据库的唯一索引 unique 键作为限制。问题来了啊，我还没有创建唯一索引捏，数据就重复了（我就是忘了，怎么滴）。

那么如何在一个普通的数据库表中删除重复的数据呢？

那我用一个例子演示一下如何操作。。。

示例

创建示例数据表

CREATE TABLE `flow_card_renewal_comparing` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `iccId` varchar(32) DEFAULT NULL COMMENT 'ICCID',
  `expireDate` date DEFAULT NULL COMMENT '到期日期',
  `result` int(5) DEFAULT NULL COMMENT '对比结果',
  `createTime` datetime DEFAULT NULL COMMENT '创建时间',
  `createBy` varchar(15) DEFAULT NULL COMMENT '创建人',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='对比结果'

创建示例数据

INSERT INTO flow_card_renewal_comparing(iccId, expireDate, `result`, createTime, createBy) VALUES 
('TEST0000111100001330', '2023-02-14', 1, '2023-02-14 15:14:38', NULL), 
('TEST0000111100001330', '2023-02-14', 1, '2023-02-14 15:14:38', NULL), 
('TEST0000111100001330', '2023-02-14', 1, '2023-02-14 15:14:38', NULL), 
('TEST0000111100001334', '2023-02-14', 3, '2023-02-14 15:14:38', NULL), 
('TEST0000111100001335', '2023-02-14', 3, '2023-02-14 15:14:38', NULL), 
('TEST0000111100001335', '2023-02-14', 3, '2023-02-14 15:14:38', NULL), 
('TEST0000111100001335', '2023-02-14', 3, '2023-02-14 15:14:38', NULL), 
('TEST0000111100001335', '2023-02-14', 3, '2023-02-14 15:14:38', NULL), 
('TEST0000111100001340', '2023-02-14', 3, '2023-02-14 15:14:38', NULL), 
('TEST0000111100001341', '2023-02-14', 3, '2023-02-14 15:14:38', NULL), 
('TEST0000111100001342', '2023-02-14', 3, '2023-02-14 15:14:38', NULL), 
('TEST0000111100001343', '2023-02-14', 3, '2023-02-14 15:14:38', NULL), 
('TEST0000111100001343', '2023-02-14', 3, '2023-02-14 15:14:38', NULL), 
('TEST0000111100001343', '2023-02-14', 2, '2023-02-14 15:14:38', NULL), 
('TEST0000111100001343', '2023-02-14', 2, '2023-02-14 15:14:38', NULL), 
('TEST0000111100001343', '2023-02-14', 2, '2023-02-14 15:14:38', NULL);

创建数据如图

现在，我们要根据主键 iccId 去重重复的数据，思路：

筛选出有重复的业务主键 iccId
查询出 1. 中最小的自增主键 id
令要删除的数据 iccId 控制在 1. 和不等于 2.中
同时删除空的业务主键数据

那么便有以下几个查询：

/*1、查询表中有重复数据的主键*/
select rd2.iccId from flow_card_renewal_comparing rd2 GROUP by rd2.iccId having count(rd2.iccId)>1

/*2、查询重复iccid中最小的id号*/
select min(id) from flow_card_renewal_comparing rd2 group by rd2.iccid having count(rd2.iccid)>1

/*3、要删除的重复数据*/
select
	*
from
	flow_card_renewal_comparing
where
	/*条件为不等于最小id的数据全删除*/
	id not in ( 
		select min(id) from flow_card_renewal_comparing rd2 group by rd2.iccid having count(rd2.iccid)>1 
	)
	and iccId in (
    /*查询有重复的iccid*/
		select rd2.iccId from flow_card_renewal_comparing rd2 GROUP by rd2.iccId having count(rd2.iccId)>1 
	)

/*4、再删除为空的数据*/
select
	*
from
	flow_card_renewal_comparing
where
	/*条件为不等于最小id的数据全删除*/
	id not in ( 
		select min(id) from flow_card_renewal_comparing rd2 group by rd2.iccid having count(rd2.iccid)>1 
	)
	and iccId in (
    /*查询有重复的iccid*/
		select rd2.iccId from flow_card_renewal_comparing rd2 GROUP by rd2.iccId having count(rd2.iccId)>1 
	)
	or iccId is null

注意一点是mysql做删除的时候会提示不能用查询的结果来做删除操作，这个时候就需要将查询的数据作为一个临时表，起别名进行删除啦。那么会变成这样：

成品

delete
from
	flow_card_renewal_comparing
where
	/*条件为不等于最小id的数据全删除*/
	id not in ( 
		select id from (select min(id) as id from flow_card_renewal_comparing group by iccid having count(iccid)>1) temp1
	)
	and iccId in (
    /*查询有重复的iccid*/
		select iccId from (select iccId from flow_card_renewal_comparing GROUP by iccId having count(iccId)>1 ) as temp2
	)
	or iccId is null