萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 查詢與刪除重複數據 SQL 實戰大全

核心場景

表 device 中 device_id 重複,需保留最新一條(按 create_date),刪除其餘。

階段式 SQL 構建

1. 查重複值及數量


SELECT device_id, COUNT(*) AS cnt
FROM device
GROUP BY device_id
HAVING COUNT(*) > 1;

2. 查看所有重複行(含時間排序)


SELECT userid, device_id, create_date
FROM device
WHERE device_id IN (
    SELECT device_id FROM device GROUP BY device_id HAVING COUNT(*) > 1
)
ORDER BY device_id, create_date DESC;

3. 定位每組保留行(最新 create_date)


SELECT device_id, MAX(create_date) AS keep_date
FROM device
GROUP BY device_id
HAVING COUNT(*) > 1;

4. 篩選出待刪除行(排除保留行)


SELECT *
FROM device
WHERE device_id IN (
    SELECT device_id FROM device GROUP BY device_id HAVING COUNT(*) > 1
)
AND create_date NOT IN (
    SELECT MAX(create_date) FROM device GROUP BY device_id HAVING COUNT(*) > 1
)
ORDER BY device_id, create_date DESC;

實戰案例:test1 表按 subject + RECEIVER 去重,保留最大 ID

表結構


CREATE TABLE test1 (
    ID INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    SENDERNAME VARCHAR(32),
    RECEIVERNAME VARCHAR(64),
    SENDER VARCHAR(64) NOT NULL,
    RECEIVER VARCHAR(64) NOT NULL,
    SUBJECT VARCHAR(512) NOT NULL,
    CONTENT TEXT NOT NULL,
    PRIORITY INT, STATUS INT,
    CREATETIME DATETIME, SENDTIME DATETIME
);

查詢待刪記錄(保留最大 ID)


SELECT a.ID, a.SUBJECT, a.RECEIVER
FROM test1 a
LEFT JOIN (
    SELECT c.SUBJECT, c.RECEIVER, MAX(c.ID) AS bid
    FROM test1 c
    WHERE status = 0
    GROUP BY RECEIVER, SUBJECT
    HAVING COUNT(1) > 1
) b ON a.ID < b.bid
WHERE a.SUBJECT = b.SUBJECT
  AND a.RECEIVER = b.RECEIVER
  AND a.ID < b.bid;

執行刪除(同條件)


DELETE a
FROM test1 a,
     (SELECT c.SUBJECT, c.RECEIVER, MAX(c.ID) AS bid
      FROM test1 c
      WHERE status = 0
      GROUP BY RECEIVER, SUBJECT
      HAVING COUNT(1) > 1) b
WHERE a.SUBJECT = b.SUBJECT
  AND a.RECEIVER = b.RECEIVER
  AND a.ID < b.bid;

性能優化提示

  • 為去重欄位建立聯合索引:ALTER TABLE test1 ADD INDEX idx_sub_rec (SUBJECT, RECEIVER);
  • 大表分批刪除:LIMIT 1000 循環執行,避免長事務鎖表
  • 使用 pt-archiver (Percona Toolkit) 低影響歸檔/刪除

通用模板:保留最早/最新/最大 ID


-- 保留最新 (MAX(create_time))
DELETE t1 FROM tbl t1
JOIN (
    SELECT dup_col, MAX(create_time) AS keep_time
    FROM tbl GROUP BY dup_col HAVING COUNT(*) > 1
) t2 ON t1.dup_col = t2.dup_col AND t1.create_time < t2.keep_time;

-- 保留最大 ID
DELETE t1 FROM tbl t1
JOIN (
    SELECT dup_col, MAX(id) AS keep_id
    FROM tbl GROUP BY dup_col HAVING COUNT(*) > 1
) t2 ON t1.dup_col = t2.dup_col AND t1.id < t2.keep_id;
copyright © 萬盛學電腦網 all rights reserved