数据表 sniper_tb 中存在主键 id,字段url,现需要在url字段上添加 unique,但由于url存在重复记录,导致添加失败。
如何删除表中多余的url重复记录,仅保持一条?
思路一
将 sniper_tb 表按url字段分组,将其中 count(url) > 1 的记录存入一个临时表 tmp中,此临时表同时包含id字段将 sniper_tb 表中 url 与 tmp.url 相同的记录找出来设置为集合 tmp2tmp2.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是特意占位出来方便替换查询限制条件的:)
思路二
将 sniper_tb 表中的记录两两比较,找出 a.url = b.url 的重复记录将这些重复记录中的最小 id 存为一个临时集合 tmp将 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;