数据库表删除重复记录只保留一条

嗯,这里是简介,主题配置内可以修改,如留空则不显示

数据库表删除重复记录只保留一条

这是一个经常会用到的业务逻辑,比如说在数据表出错,为了符合逻辑需要将多余的数据删除。

删除重复记录很简单:思路是,把不重复的记录复制到一个新的table,之后删除旧的table,然后将新的table改名。其中涉及到很多sql的知识。
 
首先备份(这是一个好的习惯):
create table old_table_name _backup 
select * from old_table_name;

创建新表:
create table new_table_name 
select * from old_table_name  
group by title_name
having count(title_name)=1;

删除旧表:
drop table old_table_name;

将新表改名:
alter table new_table_name  rename  old_table_name;
如果要删除所有重复的记录是很简单的
但是要保留重复记录中的一条就没那么简单了,
因为要保留那一条是需要业务逻辑判断的,
使用简单的mysql较难实现,
假设我们保留其中id最小的一条记录
可以写如下:
delete from item 
where
    item_id in (select 
        item_id
    from
        item
    group by item_id
    having count(item_id) > 1)
    and item_geohash not in (select 
        item_geohash
    from
        (select 
            item_id, count(*), item_geohash
        from
            item
        group by item_id
        having count(item_id) > 1) as b);


以上代码看起来是可以的,但是实际却不行,
因为在一张表上做很复杂的操作(尤其是远程操作的时候)很容易造成锁表
这里是因为在同一张表item又是更新又是查询的(根据默认的事务隔离级别,在mysql是不行的)
所以建议使用一下的方法,使用临时表的方式,(或者直接写一个函数,存储过程都行)

 

 

drop table hash_temp;
drop table id_temp;

create table id_temp select item_id from
    item
group by item_id
having count(item_id) > 1;

create table hash_temp select item_id, count(*), item_geohash from
    item
group by item_id
having count(item_id) > 1;

SET SQL_SAFE_UPDATES = 0;

delete from item 
where
    item_id in (select 
        item_id
    from
        id_temp)
    and item_geohash not in (select 
        item_geohash
    from
        hash_temp);
首先建立两个临时表,
然后再操作就不会与之前的问题了
我在用40W+条数据测试,执行了大概5分钟的时间,时间太长了。原因是我没有加索引
建议在两个临时表和原表中建立索引(我都没加),这样时间会大大减少,执行结果如下
 
如果需要保留特定的字段的数据,需要如min(id)等进行获取

 

blob.png

 

 

经过以上的处理之后发现还是有重复的数据,
查询如下:

 

blob.png

 

分析原因是:

 

delete from item 
where
    item_id in (select 
        item_id
    from
        id_temp)
    and item_geohash not in (select 
        item_geohash
    from
        hash_temp);


次数的not in的item_geohash可能还包括其他数据的字段,
此处应该是仅仅自己这条数据对应的item_geohash不删除而已
因此修改如下

 

 

 

blob.png

 

 

现在发现可以使用一张表就可以了
做到了简化
注意表hash_temp表的字段的别名是什么
需要对应,我因为没有对应查了很长时间,
(貌似mysql的子查询没有错误提示即使没有这个字段,不是很合理)
亲测可行哦~~

 

 

存储过程或函数的解决过程
 
像这么有一点业务逻辑的操作使用存储过程或者是函数很容易解决的
前面已经解决了,
我这里使用存储过程再解决一遍
算是使用mysql对我的存储过程编写做一次联系吧,嘿嘿
下面是代码:

 

drop procedure if exists test;
delimiter $$
create procedure test()
begin
	declare v_item_id varchar(45);
	declare v_item_geohash varchar(45);
	declare v_item_category varchar(45);

	declare v_id varchar(45);
	declare v_geohash varchar(45);

	declare v_dd int(9);

	declare not_fetch_ok boolean;
	declare cur cursor for select * from item;

	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET not_fetch_ok = true;

	set not_fetch_ok=false;
	set v_dd = 1;

	open cur;
	test_loop:loop
		fetch cur into v_item_id,v_item_geohash,v_item_category;
		if not_fetch_ok then
			leave test_loop;
		else
			set v_dd = v_dd +1 ;
			select item_id,item_geohash into v_id,v_geohash from hash_temp where v_item_id = item_id;
			if v_id <> null and v_item_geohash<> v_geohash then
				delete from item where item_id = v_id and item_geohash=v_item_geohash;
			end if;

		end if;
	end loop;
	close cur;

	select v_dd;

end$$
delimiter ;
call test();


具体的存储过程的业务逻辑我就不写了
和一般的高级语言没啥区别
就是一些语法的问题
相比之下我不是很喜欢mysql的存储过程的编写
更喜欢Oracle的
总感觉mysql的不像是一门语言,有很多废话....
oracle的plsql基本能算是一本语言了。

 


发表评论

必填

选填

选填

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。

Hello world. 豫ICP备16008819号-1.

Power by Z-BlogPHP  Theme by wzdaxue