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

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

CREATE DATABASE /*!32312 IF NOT EXISTS*/`test` /*!40100 DEFAULT CHARACTER SET utf8 */; USE `test`; /*Table structure for table `test` */ DROP TABLE IF EXISTS `test`; CREATE TABLE `test` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` char(20) DEFAULT NULL COMMENT '姓名',  `age` tinyint(4) DEFAULT NULL COMMENT '年龄',  `mate` tinyint(4) DEFAULT '1' COMMENT '有无配偶(1-有 0-无)',  PRIMARY KEY (`id`),  KEY `idx_name` (`name`),  KEY `idx_age` (`age`)) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

现有记录：

?

1

2

3

4

5

6

7

8

insert  into `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字段修改成唯一索引：

?

1

2

drop index idx_name on test;

alter table test add unique index (name);

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

?

1

alter ignore table test add unique idx_name (name);

它会删除重复的记录（别怕，会保留一条），然后建立唯一索引，高效而且人性化。
2.重建表方法一：
创建另外一个表，为了防止原来的表结构丢失，可以先创建一个这样的”临时表”，

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

USE `test`;

 

/*Table structure for table `test` */

 

DROP TABLE IF EXISTS `uniq_test`;

 

CREATE TABLE `uniq_test` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` char(20) DEFAULT NULL COMMENT '姓名',

  `age` tinyint(4) DEFAULT NULL COMMENT '年龄',

  `mate` tinyint(4) DEFAULT '1'COMMENT '有无配偶(1-有 0-无)',

  PRIMARY KEY (`id`),

  KEY `idx_name` (`name`),

  KEY `idx_age` (`age`)

) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

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

?

1

2

3

insert into uniq_test select * from test group by name;

drop table test;

rename table uniq_test to test;

3.删除重复记录法：
创建一个表用来存放，要删除的记录的id信息:

?

1

2

3

4

CREATE TABLE `tmp_ids` (

  `id` int(11),

  `name` char(20) 

) ENGINE=MyISAM;

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

?

1

2

3

4

CREATE TABLE `tmp_ids` (

  `id` int(11),

  `name` char(20)

) ENGINE=HEAP;

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

?

1

2

3

insert into tmp_ids select min(id),name from test group by name having count(*)>1 order by null;

deletea.* from test a,tmp_ids b where b.name=a.name anda.id>b.id;

truncate table tmp_ids;

4.效率低下方法

?

1

2

3

4

5

6

7

8

9

DELETE test AS a  FROM test AS a,

(

SELECT *

FROM  test

GROUP BY name

HAVINGcount(1) >1

order by null

) AS b

WHERE a.name = b.name AND a.id > b.id;

总结：
第一种方法历史22分钟，系统负载5左右；
第二种方法效率非常低下，把未知索引文件破坏，终止执行
第三种方法历时17分钟，其中

?

1

insert into tmp_ids select min(id),name from test group by name having count(*)>1 order by null

历时15分钟，删除动作历时2分钟，系统负载3左右
第四种方法，执行过程中，把它test的索引文件都破坏了，可见”威力”之大。