MySQL 删除重复行

分类:技术文档 - JS文档 | 阅读(3644) | 发布于:2014-05-21 15:31

MySQL 删除重复行

最近看了一些关于Mysql优化方面的东西,想整理下来。
第一个是查询优化的
1、order by
2、group by
3、limit
4、index
5、删除表中的重复行

(一)删除表中的重复行,建立复合主键
先从表中的重复行建立复合索引来说:
table name prima
col1 col2 col3 col4 col5
aa1 bb1 cc1 dd1 ee1   *
aa1 bb1 cc1 dd1 ee1   *
aa1 bb1 cc2 dd4 ee4   %
aa2 bb2 cc2 dd2 ee2   #
aa3 bb3 cc3 dd3 ee3   $
aa3 bb3 cc3 dd3 ee3   $
aa3 bb3 cc3 dd3 ee3   $
需要建立复合主键(col1,col2,col3)

网上流传着很多版本的删除重复行的方法,但经历几天的研究终于找到一种适合于mysql的最简洁高效的建立主键的方法(灰常强悍):

alter ignore table prima add primary key (col1,col2,col3);
  IGNORE是MySQL相对于标准SQL的扩展。如果在新表中有重复关键字,或者当STRICT模式启动后出现警告,则使用IGNORE控制ALTER TABLE的运行。
  如果没有指定IGNORE,当重复关键字错误发生时,复制操作被放弃,返回前一步骤。
  如果指定了IGNORE,则对于有重复关键字的行,只使用第一行,其它有冲突的行被删除。并且,对错误值进行修正,使之尽量接近正确值。

当然还有其他的解决办法:
方法一:
1、选出所有重复的列,并存入临时表中  create temporary table tmp_prima (select  *  from  prima  group  by  col1,col2,col3  having count(*)>1)
2、将原表中于临时表相同的记录删除  delete from prima using (prima,tmp_prima)where prima.col1=tmp_prima.col1 and prima.col2=tmp_prima.col2 and prima.col3=tmp_prima.col3
3、建立复合主键 alter table prima add primary key (col1,col2,col3);
4、将临时表中的记录插入原表中  insert into prima (select * from tmp_prima );

方法二:
1、增加一列自动增长的id.   alter table prima add id INT(1) NOT NULL AUTO_INCREMENT,add index id(id);
2、删除重复数据,只保留一条记录.
delete from prima using (prima,(select distinct min(id) as id ,col1,col2,col3 from prima group by col1,col2,col3 having count(1)>1) as t2 )
where prima.col1=t2.col1 and prima.col2=t2.col2 and prima.col3=t2.col3 and prima.id > t2.id;
3、删除id字段 alter table prima drop id;


方法二来源:http://www.cnblogs.com/consatan/archive/2010/12/17/1909087.html
注:mysql 与Oracle、 MS Server在delete上有一点区别,在写法上的要求不一样,当要删除一个表中的某些数据的时候不能直接写成
delete from prima where (col1,col2,col3) in (select col1,col2,col3 from prima group by col1,col2,col3 having count(1)>1) 这个样子,会报锁表错。

猜测是delte 的时候表会被锁,不让读,而改为using 标准写法后是不是生成临时表?



方法二


create   temporary   table   临时表名   select   distinct   *   from   操作的表名;   

--去掉重复后复制到临时表
    

truncate   table   操作的表名;  

--删除原表中所有记录
    
 
insert   into   操作的表名   select   *   from  临时表名;   

--把临时表的内容导入原表
    
drop   table   临时表名;   

--删除临时表

eg:

 create   temporary   table  newtable  select   distinct   *   from  oldtable;

 truncate   oldtable  haha;

 insert   into   oldertable  select   *   from  newtable;   

 drop   table   newtable;

标签:MySQL删除重复