mysql 删除重複資料_mysql表删除重複記錄方法總結及效率對比

下面這些方法在我虛拟機上做的測試，記憶體384M,交換分區1024M, test共300W資料，重複記錄3.5W，需求如題目所示，表結構如下：

CREATEDATABASE`test`;USE`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;;;

現有記錄：

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`;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的索引檔案都破壞了，可見“威力”之大；