MySQL 大批量插入,过滤掉重复数据方式
1、把重复的数据全部删除
2、把重复的数据中,只留一条,其他相同数据删除
————————————————————————————————
1、把重复的数据全部删除
1.1、首先查出来重复的name,根据分组cout来查询
SELECT name from `user` GROUP BY `name` HAVING COUNT(1) > 1
1.1、这些重复的行数据删除
DELETE FROM `user` WHERE `name` IN (SELECT name from `user` GROUP BY `name` HAVING COUNT(1) > 1)
ERROR:You can't specify target table 'user' for update in FROM clause
这是因为又更新又查询,mysql不支持,必须得将后面查询单独作为中间表,再去查一遍即可
正确的SQL
DELETE FROM `user` WHERE `name` IN (SELECT * from (SELECT name from `user` GROUP BY `name` HAVING COUNT(1) > 1) a)
2、把重复的数据中,只留一条,其他相同数据删除
2.1根据name进行分组,取出每个分组name中最小的Id
SELECT MIN(id) from `user` GROUP BY `name`
2.2保留以上SQL的数据,其他的数据就是重复的数据,重复数据查询
SELECT * FROM `user` WHERE id NOT IN (SELECT MIN(id) from `user` GROUP BY `name`)
2.3删除重复数据
DELETE FROM `user` WHERE id IN (SELECT * FROM (SELECT id FROM `user` WHERE id NOT IN (SELECT MIN(id) from `user` GROUP BY `name`)) a)