前言
一般我們將數(shù)據(jù)存儲在MySQL數(shù)據(jù)庫中般堆，它允許我們存儲重復(fù)的數(shù)據(jù)在孝。但是往往重復(fù)的數(shù)據(jù)是作廢的、沒有用的數(shù)據(jù)淮摔，那么通常我們會使用數(shù)據(jù)庫的唯一索引 unique 鍵作為限制私沮。問題來了啊，我還沒有創(chuàng)建唯一索引捏和橙，數(shù)據(jù)就重復(fù)了（我就是忘了仔燕，怎么滴）。
那么如何在一個(gè)普通的數(shù)據(jù)庫表中刪除重復(fù)的數(shù)據(jù)呢魔招？
那我用一個(gè)例子演示一下如何操作晰搀。。办斑。
示例
創(chuàng)建示例數(shù)據(jù)表
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 '對比結(jié)果',
`createTime` datetime DEFAULT NULL COMMENT '創(chuàng)建時(shí)間',
`createBy` varchar(15) DEFAULT NULL COMMENT '創(chuàng)建人',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='對比結(jié)果'
創(chuàng)建示例數(shù)據(jù)
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);
創(chuàng)建數(shù)據(jù)如圖
數(shù)據(jù)
現(xiàn)在外恕，我們要根據(jù)主鍵 iccId 去重重復(fù)的數(shù)據(jù)，思路：
- 篩選出有重復(fù)的業(yè)務(wù)主鍵
iccId - 查詢出 1. 中最小的自增主鍵
id - 令要刪除的數(shù)據(jù)
iccId控制在 1. 和 不等于 2.中 - 同時(shí)刪除空的業(yè)務(wù)主鍵數(shù)據(jù)
那么便有以下幾個(gè)查詢：
/*1乡翅、查詢表中有重復(fù)數(shù)據(jù)的主鍵*/
select rd2.iccId from flow_card_renewal_comparing rd2 GROUP by rd2.iccId having count(rd2.iccId)>1
/*2鳞疲、查詢重復(fù)iccid中最小的id號*/
select min(id) from flow_card_renewal_comparing rd2 group by rd2.iccid having count(rd2.iccid)>1
/*3、要刪除的重復(fù)數(shù)據(jù)*/
select
*
from
flow_card_renewal_comparing
where
/*條件為不等于最小id的數(shù)據(jù)全刪除*/
id not in (
select min(id) from flow_card_renewal_comparing rd2 group by rd2.iccid having count(rd2.iccid)>1
)
and iccId in (
/*查詢有重復(fù)的iccid*/
select rd2.iccId from flow_card_renewal_comparing rd2 GROUP by rd2.iccId having count(rd2.iccId)>1
)
/*4蠕蚜、再刪除為空的數(shù)據(jù)*/
select
*
from
flow_card_renewal_comparing
where
/*條件為不等于最小id的數(shù)據(jù)全刪除*/
id not in (
select min(id) from flow_card_renewal_comparing rd2 group by rd2.iccid having count(rd2.iccid)>1
)
and iccId in (
/*查詢有重復(fù)的iccid*/
select rd2.iccId from flow_card_renewal_comparing rd2 GROUP by rd2.iccId having count(rd2.iccId)>1
)
or iccId is null
注意一點(diǎn)是mysql做刪除的時(shí)候會提示不能用查詢的結(jié)果來做刪除操作尚洽，這個(gè)時(shí)候就需要將查詢的數(shù)據(jù)作為一個(gè)臨時(shí)表，起別名進(jìn)行刪除啦靶累。那么會變成這樣：
成品
delete
from
flow_card_renewal_comparing
where
/*條件為不等于最小id的數(shù)據(jù)全刪除*/
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 (
/*查詢有重復(fù)的iccid*/
select iccId from (select iccId from flow_card_renewal_comparing GROUP by iccId having count(iccId)>1 ) as temp2
)
or iccId is null
尾言
然后在這里再給數(shù)據(jù)庫的主鍵設(shè)置唯一索引啦腺毫！
行啦癣疟，先這樣吧。
