快捷導航

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

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

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

前言

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

那么如何在一個普通的數(shù)據(jù)庫表中刪除重復的數(shù)據(jù)呢？

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

示例

創(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)建時間',
  `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 去重重復的數(shù)據(jù)，思路：

篩選出有重復的業(yè)務(wù)主鍵 iccId
查詢出 1. 中最小的自增主鍵 id
令要刪除的數(shù)據(jù) iccId 控制在 1. 和不等于 2.中
同時刪除空的業(yè)務(wù)主鍵數(shù)據(jù)

那么便有以下幾個查詢：

/*1、查詢表中有重復數(shù)據(jù)的主鍵*/
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、要刪除的重復數(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 (
    /*查詢有重復的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 (
    /*查詢有重復的iccid*/
        select rd2.iccId from flow_card_renewal_comparing rd2 GROUP by rd2.iccId having count(rd2.iccId)>1 
    )
    or iccId is null

注意一點是mysql做刪除的時候會提示不能用查詢的結(jié)果來做刪除操作，這個時候就需要將查詢的數(shù)據(jù)作為一個臨時表，起別名進行刪除啦。那么會變成這樣：

成品

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 (
    /*查詢有重復的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