最新消息: 生命不止,奋斗不息……

mysql删除重复记录的方法

Mysql php 703浏览 1评论

有时在项目开发过程中,我们需要清理数据表中的重复的记录,下面这些方法在我虚拟机上做的测试,内存384M,交换分区1024M, test共300W数据,重复记录3.5W,需求如题目所示,表结构如下:
2008123162556

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 */;

现有记录:

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字段修改成唯一索引:

drop index idx_name on test;
alter table test add unique index (name);

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

alter ignore table test add unique idx_name (name);

它会删除重复的记录(别怕,会保留一条),然后建立唯一索引,高效而且人性化。

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

CREATE TABLE `tmp_ids` (
`id` int(11),
`name` char(20)
) ENGINE=MyISAM;

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

CREATE TABLE `tmp_ids` (
`id` int(11),
`name` char(20)
) ENGINE=HEAP;

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

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;

第一种方法历史22分钟,系统负载5左右;
第二种方法历时17分钟,其中

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

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

转载请注明:IT世界 » mysql删除重复记录的方法

您必须 登录 才能发表评论!

网友最新评论 (1)