mysql删除重复记录

郭玲昕

有些时候我们在插入数据的时候可能是循环的问题，就会插入很多重复的数据，但是我们一条一条删除就会很麻烦，所以我们来个高效快捷的删除方式

首先：创建一个表，不要加自增

CREATE TABLE IF NOT EXISTS `caiji_ym_list` (  `newid` int(8) NOT NULL COMMENT '新闻的id',  `pinurl` text NOT NULL COMMENT '拼接之后的url',  KEY `newid` (`newid`)) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;


插入重复的测试数据

INSERT INTO `caiji_ym_list` VALUES (285003, '&callback=fn1');

INSERT INTO `caiji_ym_list` VALUES (285003, '&callback=fn1');


INSERT INTO `caiji_ym_list` VALUES (285004, '&callback=fn2');

INSERT INTO `caiji_ym_list` VALUES (285004, '&callback=fn2');


INSERT INTO `caiji_ym_list` VALUES (285005, '&callback=fn3');

INSERT INTO `caiji_ym_list` VALUES (285005, '&callback=fn3');


INSERT INTO `caiji_ym_list` VALUES (285006, '&callback=fn4');

INSERT INTO `caiji_ym_list` VALUES (285006, '&callback=fn4');


查询显示全部的重复的数据

SELECT a.newid,a.pinurlFROM `caiji_ym_list` as a,(  SELECT `newid`,`pinurl`  FROM `caiji_ym_list`  GROUP BY `newid`,`pinurl`  HAVING COUNT(1) > 1) AS `b`WHERE a.`newid` = b.`newid`  AND a.`pinurl` = b.`pinurl` ORDER BY a.newid;


需要表上加一个自增的键值

/* 为表添加自增长的id字段 */ALTER TABLE `caiji_ym_list` ADD `id` INT(8) NOT NULL AUTO_INCREMENT, ADD INDEX `id`(`id`);


删除重复的数据

DELETE FROM `caiji_ym_list`USING `caiji_ym_list`,(  SELECT DISTINCT MIN(`id`) AS `id`,`newid`,`pinurl`  FROM `caiji_ym_list`  GROUP BY `newid`,`pinurl`  HAVING COUNT(1) > 1) AS `b`WHERE `caiji_ym_list`.`newid` = `b`.`newid`  AND `caiji_ym_list`.`pinurl` = `b`.`pinurl`  AND `caiji_ym_list`.`id` <> `b`.`id`;


查看相应数据结果

[SQL]DELETE FROM `caiji_ym_list`USING `caiji_ym_list`,(  SELECT DISTINCT MIN(`id`) AS `id`,`newid`,`pinurl`  FROM `caiji_ym_list`  GROUP BY `newid`,`pinurl`  HAVING COUNT(1) > 1) AS `b`WHERE `caiji_ym_list`.`newid` = `b`.`newid`  AND `caiji_ym_list`.`pinurl` = `b`.`pinurl`  AND `caiji_ym_list`.`id` <> `b`.`id`;受影响的行: 3