mysql数据库重复记录过滤删除解决

最新推荐文章于 2022-01-13 17:20:50 发布

weixin_33971130

最新推荐文章于 2022-01-13 17:20:50 发布

阅读量96

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/u/1377923/blog/710355

版权

2019独角兽企业重金招聘Python工程师标准>>>

删除重复记录

#1、查询重复记录

SELECT id,COUNT(*) AS total,songurl FROM HFMedia.HFSongs GROUP BY songurl HAVING total > 1 ORDER BY total DESC

#2、建立临时表
CREATE TEMPORARY TABLE tbl_hfsongs( id INT(10) ,number INT(5), url VARCHAR(255) , KEY pk(id) ) ENGINE=MEMORY;

#3、查数据插入临时表

INSERT INTO tbl_hfsongs SELECT id,COUNT(*) AS total,songurl FROM HFMedia.HFSongs GROUP BY songurl HAVING total > 1 ORDER BY total DESC ;

#4、测试生产的sqls

SELECT CONCAT('delete from FROM HFMedia.HFSongs where songurl = \'',uniq,'\' and id != ',id) FROM tbl_hfsongs LIMIT 2;

#5、生成执行脚本、
SELECT CONCAT('delete from FROM HFMedia.HFSongs where songurl = \'',uniq,'\' and id != ',id,';') INTO OUTFILE '/tmp/delect_sb.sql' FROM tbl_hfsongs ;

#6、执行脚本

mysql>source /tmp/delect_sb.sql

#7、测试清理重复数据后情况

SELECT id,COUNT(*) AS total,songurl FROM HFMedia.HFSongs GROUP BY songurl HAVING total > 1 ORDER BY total DESC

#8，手动释放临时表

mysql>truncate tbl_hfsongs;

mysql>drop table tbl_hfsongs;

转载于:https://my.oschina.net/u/1377923/blog/710355