mysql 删除重复数据_mysql表删除重复记录方法总结及效率对比

下面这些方法在我虚拟机上做的测试，内存384M,交换分区1024M, test共300W数据，重复记录3.5W，需求如题目所示，表结构如下：

CREATEDATABASE/*!32312 IF NOT EXISTS*/`test`/*!40100 DEFAULT CHARACTER SET utf8*/;USE`test`;/*Table structure for table `test`*/DROPTABLEIFEXISTS`test`;CREATETABLE`test` (

`id`int(11)NOTNULLAUTO_INCREMENT,

`name`char(20)DEFAULTNULLCOMMENT'姓名',

`age`tinyint(4)DEFAULTNULLCOMMENT'年龄',

`mate`tinyint(4)DEFAULT'1'COMMENT'有无配偶(1-有 0-无)',PRIMARYKEY(`id`),KEY`idx_name` (`name`),KEY`idx_age` (`age`)

) ENGINE=MyISAM AUTO_INCREMENT=10DEFAULTCHARSET=utf8;/*!40101 SET SQL_MODE=@OLD_SQL_MODE*/;/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS*/;

现有记录：

insertinto`test`(`id`,`name`,`age`,`mate`)values(2,'aaaaa',28,0),

(3,'bbbb',23,0),

(4,'cccc',25,1),

(5,'dddd',26,0),

(6,'eeee',24,0),

(7,'fffff',18,0),

(8,'eeee',40,1),

(9,'eeee',60,1);

想去掉name重名的记录，方法如下：

1.给name字段修改成唯一索引：

dropindexidx_nameontest;altertabletestadduniqueindex(name);

这样当向表中添加相同记录的时候，会返回1062的添加失败信息。

但是有一种情况是表中已经有n个重复的记录，这时候我们才想起来要添加唯一索引，再执行上面的操作时，数据库会告诉你已经有重复的记录了，建立索引失败，这时候，我们可以用下面的操作：

alterignoretabletestadduniqueidx_name(name);

它会删除重复的记录(别怕，会保留一条)(但是这个命令在MySQL5.1.37之前是可以的，在5.1.48以后就行不通了)，然后建立唯一索引，高效而且人性化。

另外需要注意的是alter ignore table 在percona版本的MySQL行不通，因为它创建索引的方式是：fast index creation

2.重建表方法一：

创建另外一个表，为了防止原来的表结构丢失，可以先创建一个这样的“临时表”，

USE`test`;/*Table structure for table `test`*/DROPTABLEIFEXISTS`uniq_test`;CREATETABLE`uniq_test` (

`id`int(11)NOTNULLAUTO_INCREMENT,

`name`char(20)DEFAUL TNULLCOMMENT'姓名',

`age`tinyint(4)DEFAUL TNULLCOMMENT'年龄',

`mate`tinyint(4)DEFAULT'1'COMMENT'有无配偶(1-有 0-无)',PRIMARYKEY(`id`),KEY`idx_name` (`name`),KEY`idx_age` (`age`)

) ENGINE=MyISAM AUTO_INCREMENT=10DEFAULTCHARSET=utf8;

从test表中查找数据，添加到uniq_test中：

insertintouniq_testselect*fromtestgroupbyname;

drop table test;

rename table uniq_test to test;

3.删除重复记录法：

创建一个表用来存放，要删除的记录的id信息:

CREATETABLE`tmp_ids` (

`id`int(11),

`name` char(20)

) ENGINE=MyISAM;

如果要删除的记录不多的话，可以把这个表创建成内存表形式：

CREATETABLE`tmp_ids` (

`id`int(11),

`name` char(20)

) ENGINE=HEAP;

然后在test表中删除重复记录：

insertintotmp_idsselectmin(id),namefromtestgroupbynamehavingcount(*)>1order by null;deletea.*fromtest a,tmp_ids bwhere b.name=a.name anda.id>b.id;truncatetabletmp_ids;

4.效率低下方法

DELETEtestASaFROMtestASa,

(SELECT*FROMtestGROUPBYnameHAVINGcount(1)>1orderbynull)ASbWHEREa.name=b.nameANDa.id>b.id;

总结：

第一种方法历史22分钟，系统负载5左右；

第二种方法效率非常低下，把未知索引文件破坏，终止执行

第三种方法历时17分钟，其中

insertintotmp_idsselectmin(id),namefromtestgroupbynamehavingcount(*)>1orderbynull

历时15分钟，删除动作历时2分钟，系统负载3左右

第四种方法，执行过程中，把它test的索引文件都破坏了，可见“威力”之大；