快捷導(dǎo)航

刪除MySQL表中重復(fù)數(shù)據(jù)詳解

更新時(shí)間：2023年06月15日 17:07:24 作者：我是一顆小虎牙_

這篇文章主要為大家介紹了刪除MySQL表中重復(fù)數(shù)據(jù)詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪

前言

一般我們將數(shù)據(jù)存儲(chǔ)在MySQL數(shù)據(jù)庫(kù)中，它允許我們存儲(chǔ)重復(fù)的數(shù)據(jù)。但是往往重復(fù)的數(shù)據(jù)是作廢的、沒(méi)有用的數(shù)據(jù)，那么通常我們會(huì)使用數(shù)據(jù)庫(kù)的唯一索引 unique 鍵作為限制。問(wèn)題來(lái)了啊，我還沒(méi)有創(chuàng)建唯一索引捏，數(shù)據(jù)就重復(fù)了（我就是忘了，怎么滴）。

那么如何在一個(gè)普通的數(shù)據(jù)庫(kù)表中刪除重復(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 '對(duì)比結(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='對(duì)比結(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
令要?jiǎng)h除的數(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號(hào)*/
select min(id) from flow_card_renewal_comparing rd2 group by rd2.iccid having count(rd2.iccid)>1
/*3、要?jiǎng)h除的重復(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í)候會(huì)提示不能用查詢的結(jié)果來(lái)做刪除操作，這個(gè)時(shí)候就需要將查詢的數(shù)據(jù)作為一個(gè)臨時(shí)表，起別名進(jìn)行刪除啦。那么會(huì)變成這樣：

成品

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)&gt;1) temp1
    )
    and iccId in (
    /*查詢有重復(fù)的iccid*/
        select iccId from (select iccId from flow_card_renewal_comparing GROUP by iccId having count(iccId)&gt;1 ) as temp2
    )
    or iccId is null