mysql表删除重复记录方法总结及效率对比_mysql删除重复行翻译-CSDN博客

下面这些方法在我虚拟机上做的测试，内存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 */;

现有记录：

 
        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.重建表方法一：
创建另外一个表，为了防止原来的表结构丢失，可以先创建一个这样的”临时表”，

 
        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; 
       
        delete 
         a.* from test a,tmp_ids b where b.name=a.name  
        and 
         a.id>b.id; 
       
        truncate table tmp_ids;

4.效率低下方法

 
        DELETE  
         test AS a  FROM test AS a, 
       
        ( 
       
        SELECT *  
       
        FROM  test 
       
        GROUP BY name  
       
        HAVING  
        count 
        (1) >1 
       
        order by null 
       
        ) AS b 
       
        WHERE a.name = b.name AND a.id > b.id;