測試資料準備

CREATE TABLE t1 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    addr VARCHAR(20) NOT NULL
) ENGINE=InnoDB;

INSERT INTO t1 (name, addr) VALUES
('abc','123'),('abc','123'),('abc','321'),('abc','123'),
('xzy','123'),('xzy','456'),('xzy','456'),('xzy','456'),
('xzy','789'),('xzy','987'),('xzy','789'),
('ijk','147'),('ijk','147'),('ijk','852'),
('opq','852'),('opq','963'),('opq','741'),
('tpk','741'),('tpk','963'),('tpk','963'),
('wer','546'),('wer','546'),('once','546');

場景一：完全重複 (所有欄位相同)，保留一筆

-- MySQL 8.0+ 窗口函數 (推薦)
WITH cte AS (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY name, addr ORDER BY id) AS rn
    FROM t1
)
DELETE FROM t1 WHERE id IN (SELECT id FROM cte WHERE rn > 1);

-- 通用法：建新表去重
CREATE TABLE t1_dedup AS SELECT DISTINCT * FROM t1;
DROP TABLE t1;
RENAME TABLE t1_dedup TO t1;
ALTER TABLE t1 ADD PRIMARY KEY (id);  -- 重建主鍵

場景二：關鍵欄位重複 (name, addr)，保留第一筆 (最小 id)

-- 方法 A: 多表 DELETE (MySQL 支援)
DELETE t1 FROM t1
INNER JOIN (
    SELECT MIN(id) AS keep_id, name, addr
    FROM t1
    GROUP BY name, addr
) t2 ON t1.name = t2.name AND t1.addr = t2.addr
WHERE t1.id > t2.keep_id;

-- 方法 B: 自增輔助欄位 (舊版通用)
ALTER TABLE t1 ADD COLUMN auto_id INT AUTO_INCREMENT PRIMARY KEY;
DELETE t1 FROM t1
JOIN (
    SELECT MIN(auto_id) AS keep_id
    FROM t1
    GROUP BY name, addr
) t2 ON t1.auto_id > t2.keep_id
   AND t1.name = (SELECT name FROM t1 WHERE auto_id = t2.keep_id)
   AND t1.addr = (SELECT addr FROM t1 WHERE auto_id = t2.keep_id);
ALTER TABLE t1 DROP COLUMN auto_id;

查找重複資料

-- 1. 統計重複組合
SELECT name, addr, COUNT(*) AS cnt
FROM t1
GROUP BY name, addr
HAVING COUNT(*) > 1;

-- 2. 顯示所有重複列 (含首筆)
SELECT * FROM t1
WHERE (name, addr) IN (
    SELECT name, addr FROM t1
    GROUP BY name, addr HAVING COUNT(*) > 1
);

-- 3. 找出需刪除的 id (保留最小 id)
SELECT id FROM t1
WHERE id NOT IN (
    SELECT MIN(id) FROM t1 GROUP BY name, addr
);

預防重複：加唯一索引

ALTER TABLE t1 ADD UNIQUE INDEX uk_name_addr (name, addr);
-- 之後 INSERT 重複會報錯，改用：
INSERT IGNORE INTO t1 (name, addr) VALUES ('abc','123');
-- 或
INSERT INTO t1 (name, addr) VALUES ('abc','123')
ON DUPLICATE KEY UPDATE name=name;  -- 無實際更新，忽略衝突