mysql怎么查询重复数据并只保留一条_使用临时表与Group By过滤-mysql教程-PHP中文网

mysql查重复数据须用group by+having（因count是聚合函数，where中不可用），如select email,count() from users group by email having count()>1；删重复留一条推荐建临时表存每组min(id)，再delete not in该表，并加唯一索引防重。

mysql怎么查询重复数据并只保留一条_使用临时表与group by过滤

查重复数据用 GROUP BY + HAVING，不是 WHERE

直接写 WHERE COUNT(*) > 1 会报错，因为 COUNT 是聚合函数，不能在 WHERE 里用。必须先分组，再筛组——也就是用 HAVING。

比如查 email 字段重复的记录：

SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING cnt > 1;

这个结果只告诉你哪些 email 重复、重复几次，不返回原始行。如果要看到具体是哪几条重复，得再套一层 JOIN 或子查询。

只查重复值本身（如 email 列表），用上面的写法就够了
要查完整重复行（含 id、name 等），得关联原表，例如：SELECT u.* FROM users u INNER JOIN (上面的 GROUP BY 查询) t ON u.email = t.email
GROUP BY 的字段必须和 SELECT 中的非聚合字段完全一致，否则在 ONLY_FULL_GROUP_BY 模式下会报错

保留一条用临时表最稳，尤其 MySQL 5.7 及以下

想删重复、留一条，又不想写复杂自连接或窗口函数？临时表是最直白、兼容性最好的路。它绕开了 MySQL “不能在 DELETE 中直接引用目标表” 的限制，也避免了 ROW_NUMBER() 在低版本不可用的问题。

三步走：

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

建临时表，存每组要保留的最小 id：CREATE TEMPORARY TABLE keep_ids AS SELECT MIN(id) AS id FROM users GROUP BY email
删掉不在这个集合里的记录：DELETE FROM users WHERE id NOT IN (SELECT id FROM keep_ids)
删完立刻加唯一索引：ALTER TABLE users ADD UNIQUE KEY uk_email (email)，防止后续再插入重复

注意：NOT IN 遇到 NULL 会整个条件失效，所以确保 keep_ids.id 没有 NULL；如果原始表 email 允许为空，得提前处理或改用 LEFT JOIN ... IS NULL 方式。

GROUP BY 本身不能“选最新/最旧”，得靠排序+子查询兜底

GROUP BY email 不保证返回的是时间最新或 ID 最小的那条——MySQL 5.7 默认行为是随机取一条（取决于存储引擎和执行计划），8.0 后在 ONLY_FULL_GROUP_BY 下甚至不允许这么写。

想明确保留最新一条（比如按 created_at 降序），必须显式控制顺序：

方案一（推荐）：用窗口函数（MySQL 8.0+）ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC)，然后取 rn = 1
方案二（兼容老版本）：先按时间倒序查出所有行，再 GROUP BY email，但需配合派生表：SELECT * FROM (SELECT * FROM users ORDER BY created_at DESC) t GROUP BY email —— 这种写法在部分 MySQL 版本中能 work，但属于隐式行为，不保证稳定，生产环境慎用
方案三（最稳妥）：用 JOIN 找每组最大时间：SELECT u1.* FROM users u1 LEFT JOIN users u2 ON u1.email = u2.email AND u1.created_at

临时表方案的性能和事务边界要注意

临时表看起来简单，但大数据量时容易踩两个坑：

建临时表过程本身会锁表或产生大量临时磁盘 I/O，线上表建议在低峰期操作，或先 CREATE TABLE like 建结构，再 INSERT INTO ... SELECT 分批导入
临时表只在当前 session 生效，如果脚本中途断开，keep_ids 就没了，后续 DELETE 会失败；建议把建表、删数、加索引三步写进一个事务块，或至少用 START TRANSACTION 包住前两步
如果原表有外键或触发器，删数据前确认是否影响关联逻辑；必要时先禁用再恢复（SET FOREIGN_KEY_CHECKS=0）

真正麻烦的从来不是语法，而是删之前没确认好“哪一条才算‘该保留’的那条”，以及删完没加约束导致问题复发。