AI摘要
文章介绍了在MySQL中删除重复type数据的方法，通过GROUP BY和HAVING找出重复项，结合MIN函数确定保留的最小id，构建子查询条件后使用DELETE语句配合临时表执行删除，避免直接删除查询结果导致的错误。核心是利用子查询和临时表确保数据一致性，先筛选出需要删除的非最小id，再通过两层嵌套子查询和虚拟表实现精准删除，最终保留每类type中id最小的记录。
查出重复的type
SELECT type FROM table GROUP BY type HAVING count(type) > 1;
查出重复的type数据中最小的id
SELECT min(id) FROM table GROUP BY type HAVING count(type) > 1;
查出重复的type数据中非最小的id(需要删除的)
SELECT id FROM table WHERE type in(
SELECT type FROM table GROUP BY type HAVING count(type) > 1)
AND id not IN(SELECT min(id) FROM table GROUP BY type HAVING count(type) > 1);
在Mysql中是不能删除查询出来的记录，而是要通过一张临时表来解决
SELECT id from (
SELECT id FROM table WHERE type in(
SELECT type FROM table GROUP BY type HAVING count(type) > 1)
AND id not IN(SELECT min(id) FROM table GROUP BY type HAVING count(type) > 1)
) as t;
删除type重复的数据(只保留一条，保留最小id的)
DELETE FROM table WHERE id IN (
SELECT id from (
SELECT id FROM table WHERE type in(
SELECT type FROM table GROUP BY type HAVING count(type) > 1)
AND id not IN(SELECT min(id) FROM table GROUP BY type HAVING count(type) > 1)
) as t
);
返回首页
最后更新: 2025-11-11
