看了网上方案,很多用到了关键字in,in适合类别少用的才效率高呀,而对于百万数据很多相同的话,直接导致索引失效,那就失去了这个关键字的意义了。
我这里的方案是用存储过程:首先对有重复的name分组及查询出来,然后循环删除重复数据,相同的name只保留最大id的那一条。
下面直接上存储过程:
BEGIN
DECLARE v_name INT(32);
DECLARE v_maxid INT(10);
DECLARE _done INT(1) DEFAULT 0;
# 查询出所有重复数据
DECLARE _cur CURSOR FOR
SELECT t1.name, MAX(t1.id) maxid
FROM duplicated_user t1,
(
SELECT `name` FROM duplicated_user
GROUP BY `name`
HAVING COUNT(name) > 1
) t2
WHERE t1.name = t2.name
GROUP BY `name`;
# 错误定义,标记循环结束
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _done = 1;
OPEN _cur;
FETCH _cur INTO v_name, v_maxid;
# 循环删除重复数据
WHILE ( _done <> 1) DO
# 相同的name只保留最大id的那一条
DELETE FROM duplicated_user WHERE id < v_maxid AND name = v_name;
FETCH _cur INTO v_name, v_maxid;
END WHILE;
CLOSE _cur;
END
这是duplicated_user
表的结构,我这里是测试,所以就两个字段哈。
那怎么很快的制造百万数据出来呢,请戳这篇博文:MySQL使用存储过程30秒插入百万数据!!!
好了,有了百万数据后,我们执行存储过程可能发生以下报错:
Out of range value for column
解决办法:
修改my.ini,将
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
修改为
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
然后执行大功告成
直接只有一条最大id的数据,其他相同的全部删除了
我看了下不到30秒,就删除了160多万数据,这里我对name还没用索引,用了索引的话快到你没话说。。。
好了,这样就解决了问题了,不知道大家有没有其他的方案,欢迎一起探讨哈。