删除Mysql数据表中多余的重复记录的sql语句

数据表 sniper_tb 中存在主键 id，字段url，现需要在url字段上添加 unique，但由于url存在重复记录，导致添加失败。

如何删除表中多余的url重复记录，仅保持一条？

思路一

1. 将 sniper_tb 表按url字段分组，将其中 count(url) > 1 的记录存入一个临时表 tmp中，此临时表同时包含id字段
2. 将 sniper_tb 表中 url 与 tmp.url 相同的记录找出来设置为集合 tmp2
3. tmp2.id 不在临时表 tmp.id 中的记录，则为最终需要删除的记录

以上思路的select sql语句如下：

select id from sniper_tb where url in (select tmp.url from ( select url,id from sniper_tb where 1=1 group by url having count(url) > 1) tmp) and id not in (select tmp.id from ( select url,id from sniper_tb where 1=1 group by url having count(url) > 1) tmp)

将其中的 sniper_tb、id、url 替换成你本地对应的数据表及字段即可，将最开始的 select id 替换成 delete 即可删除这些多余的重复记录。

以上语句中的 where 1=1 是特意占位出来方便替换查询限制条件的：）

思路二

1. 将 sniper_tb 表中的记录两两比较，找出 a.url = b.url 的重复记录
2. 将这些重复记录中的最小 id 存为一个临时集合 tmp
3. 将 sniper_tb 表中id > tmp.id 的重复记录删除

对应的 select sql 语句如下：

select * from sniper_tb a where id > (select min(id) from sniper_tb b where a.url=b.url)

但在mysql中，直接将 select 替换成 delete语句会出现如下报错：

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'a where id > (select min(id) from sniper_tb b where a.url=b.url)' at line 1

mysql的delete写法有挺多限制，比较好的办法就是先 create 一个临时表，用完之后再drop掉，以上语句的 delete 实现为：

create table tmp as select id from sniper_tb a where id > (select min(id) from sniper_tb b where a.url=b.url);
delete from sniper_tb where id in(select id from tmp);
drop table tmp;

 

 

参考资料

• MySql报错： You can't specify target table 'table name' for update in FROM clause

 

 

 

转载于:https://www.cnblogs.com/kaiye/p/3149021.html