MySQL删除重复数据，保留一条

前言

表里有重复数据，同一个用户存了好几条。怎么删除重复数据，只保留一条？

一、先查有哪些重复数据

按字段查重复

SELECT name, COUNT(*) as cnt 
FROM users 
GROUP BY name 
HAVING cnt > 1;

查重复的完整记录

SELECT * FROM users 
WHERE name IN (
    SELECT name FROM users 
    GROUP BY name 
    HAVING COUNT(*) > 1
)
ORDER BY name;

二、删除重复数据，保留一条

方法1：用自连接（推荐）

DELETE u1 FROM users u1
INNER JOIN users u2
WHERE u1.id > u2.id AND u1.name = u2.name;

意思： 同一个name，id大的删掉，保留id小的。

方法2：用NOT IN

DELETE FROM users 
WHERE id NOT IN (
    SELECT min_id FROM (
        SELECT MIN(id) as min_id 
        FROM users 
        GROUP BY name
    ) as tmp
);

方法3：用临时表

-- 第一步：建临时表存要保留的id
CREATE TABLE tmp AS
SELECT MIN(id) as min_id FROM users GROUP BY name;

-- 第二步：删除不在临时表里的
DELETE FROM users WHERE id NOT IN (SELECT min_id FROM tmp);

-- 第三步：删临时表
DROP TABLE tmp;

三、按多个字段去重

比如name和email都重复才算重复：

DELETE u1 FROM users u1
INNER JOIN users u2
WHERE u1.id > u2.id 
AND u1.name = u2.name 
AND u1.email = u2.email;

四、保留最新的一条

不是保留id最小的，而是保留最新创建的：

DELETE u1 FROM users u1
INNER JOIN users u2
WHERE u1.created_at < u2.created_at 
AND u1.name = u2.name;

五、批量去重前先备份

重要！ 删之前先备份：

CREATE TABLE users_bak AS SELECT * FROM users;

确认没问题再删。

六、怎么防止重复数据

加唯一索引

ALTER TABLE users ADD UNIQUE INDEX uk_name (name);

加了唯一索引，重复数据就插不进去了。

插入前先查

// PHP示例
$exists = $db->query("SELECT id FROM users WHERE name = '$name'");
if ($exists) {
    echo "已存在";
} else {
    $db->query("INSERT INTO users ...");
}

用INSERT IGNORE

INSERT IGNORE INTO users (name) VALUES ('张三');

重复了就忽略，不报错。

用ON DUPLICATE KEY UPDATE

INSERT INTO users (name, count) VALUES ('张三', 1)
ON DUPLICATE KEY UPDATE count = count + 1;

重复了就更新，不插入新的。

常见坑

坑1：删完发现数据没了

删重复的时候把正常数据也删了。

原因： 关联条件写错了。

解决： 先备份，再用SELECT确认要删哪些：

SELECT u1.* FROM users u1
INNER JOIN users u2
WHERE u1.id > u2.id AND u1.name = u2.name;

确认没问题再把SELECT改成DELETE。

坑2：大表删太慢

几百万条数据，删半天删不完。

解决： 分批删，每次删1000条：

DELETE FROM users WHERE ... LIMIT 1000;

坑3：自连接删不干净

有的重复数据没删掉。

原因： 字段值有NULL，NULL=NULL是unknown。

解决： 加NULL判断：

DELETE u1 FROM users u1
INNER JOIN users u2
WHERE u1.id > u2.id 
AND (u1.name = u2.name OR (u1.name IS NULL AND u2.name IS NULL));

坑4：报错"You can't specify target table for update in FROM clause"

MySQL不允许先查再删同一张表。

解决： 套一层临时表：

DELETE FROM users 
WHERE id NOT IN (
    SELECT min_id FROM (
        SELECT MIN(id) as min_id FROM users GROUP BY name
    ) as tmp
);

总结

去重三步：

1. 先查：GROUP BY ... HAVING COUNT(*) > 1
2. 再确认：SELECT出来看哪些要删
3. 最后删：自连接DELETE

记住： 删之前务必备份！

遇到问题加QQ23979811 协助处理