-- 查找完全重復的記錄
SELECT * FROM table_name
WHERE (col1, col2, ..., coln) IN (
SELECT col1, col2, ..., coln
FROM table_name
GROUP BY col1, col2, ..., coln
HAVING COUNT(*) > 1
);

SELECT * FROM user_config
WHERE (user_id, config_key, config_value) IN (
SELECT user_id, config_key, config_value
FROM user_config
GROUP BY user_id, config_key, config_value
HAVING COUNT(*) > 1
);

-- 按核心字段分組，查找重復記錄
SELECT core_col1, core_col2, COUNT(*) AS duplicate_count
FROM table_name
GROUP BY core_col1, core_col2
HAVING COUNT(*) > 1;

-- 查看重復手機號及重復次數
SELECT phone, COUNT(*) AS duplicate_count
FROM users
GROUP BY phone
HAVING COUNT(*) > 1;
-- 查看重復手機號對應的完整記錄
SELECT * FROM users
WHERE phone IN (
SELECT phone
FROM users
GROUP BY phone
HAVING COUNT(*) > 1
)
ORDER BY phone;

-- 查找狀態(tài)為"已支付"的重復訂單
SELECT order_no, COUNT(*) AS duplicate_count
FROM orders
WHERE status = 'PAID'
GROUP BY order_no
HAVING COUNT(*) > 1;

-- 為重復手機號的記錄編號（按注冊時間排序）
SELECT
id, phone, register_time,
ROW_NUMBER() OVER (PARTITION BY phone ORDER BY register_time) AS rn
FROM users;

-- 保留重復手機號中id最小的記錄（即最早插入的記錄）
DELETE FROM users
WHERE id NOT IN (
SELECT min_id FROM (
-- 子查詢嵌套避免"不能從同表查詢并刪除"的限制
SELECT MIN(id) AS min_id
FROM users
GROUP BY phone
HAVING COUNT(*) > 1
) AS temp
);

-- 保留完全重復記錄中一條（需指定所有字段）
DELETE t1 FROM table_name t1
JOIN table_name t2
ON t1.col1 = t2.col1
AND t1.col2 = t2.col2
AND ...
AND t1.coln = t2.coln
WHERE t1.ctid < t2.ctid; -- 利用隱藏列ctid區(qū)分物理位置（僅InnoDB有效）

-- 保留重復手機號中注冊時間最新的記錄
DELETE t1 FROM users t1
JOIN users t2
ON t1.phone = t2.phone
AND t1.register_time < t2.register_time; -- t1為舊記錄

-- 每次刪除1000條重復記錄（循環(huán)執(zhí)行至無重復）
DELETE FROM users
WHERE id IN (
SELECT id FROM (
SELECT id
FROM (
SELECT
id,
ROW_NUMBER() OVER (PARTITION BY phone ORDER BY register_time) AS rn
FROM users
) AS t
WHERE rn > 1
LIMIT 1000 -- 限制單次刪除數量
) AS temp
);

-- 為手機號添加唯一索引，阻止重復插入
CREATE UNIQUE INDEX uk_users_phone ON users(phone);
-- 復合唯一索引（如同一用戶的配置鍵唯一）
CREATE UNIQUE INDEX uk_user_config ON user_config(user_id, config_key);

-- 插入時忽略重復記錄（不報錯，返回警告）
INSERT IGNORE INTO users (phone, name)
VALUES ('13800138000', '張三');
-- 重復時替換舊記錄（刪除舊記錄后插入新記錄）
REPLACE INTO users (phone, name)
VALUES ('13800138000', '張三');

// Java示例：插入前檢查手機號是否已存在
public boolean addUser(User user) {
// 先查詢是否存在重復手機號
if (userDao.existsByPhone(user.getPhone())) {
throw new DuplicateException("手機號已注冊");
}
return userDao.insert(user) > 0;
}