MySQL去除“关联表”重复数据，以建立联合唯一索引

前言

为来凤等地区用户提供了全套网页设计制作服务，及来凤网站建设行业解决方案。主营业务为网站建设、做网站、来凤网站设计，以传统方式定制建设网站，并提供域名空间备案等一条龙服务，秉承以专业、用心的态度为用户提供真诚的服务。我们深信只要达到每一位用户的要求，就会得到认可，从而选择与我们长期合作。这样，我们也可以走得更远！

昨天遇到一个问题，需要对一张关系表进行重构和优化。然而这张关系表由于已有代码没有注重并发导致了很多的脏数据，即重复数据。

表名thread_recommend，帖子推荐表，为两个实体user_id和thread_id的(推荐)关系表，表结构很简单如下：

 
 
 
   
  
  
  /*用户推荐帖子记录表*/   
  
  
     
  
  
  CREATE TABLE `thread_recommend` (   
  
  
     
  
  
  `id` int(11) NOT NULL AUTO_INCREMENT,   
  
  
     
  
  
  `thread_id` int(11) DEFAULT NULL COMMENT '被用户推荐的帖子编号',   
  
  
     
  
  
  `user_id` int(11) DEFAULT NULL COMMENT '推荐该帖子的用户编号',   
  
  
     
  
  
  `status` int(11) DEFAULT '1' COMMENT '状态0 取消推荐，1推荐',   
  
  
     
  
  
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '推荐时间',   
  
  
     
  
  
  PRIMARY KEY (`id`),   
  
  
     
  
  
  KEY `userid` (`user_id`) USING BTREE   
  
  
     
  
  
  ) ENGINE=InnoDB;

问题在于，由于代码不规范，在高并发时(或数据库压力大时造成的延时积压时)会出现多个(相同thread_id和user_id的)组合，如下：

之后你们懂的，各种和原想不一致的神奇bug喷涌而出，比如：

我刚刚取消了推荐，怎么还显示我推荐着!!

显示的总推荐数怎么和实际推荐用户加起来不一样!!

解决方案一：使用insert where not exists语句

声明：此方案并不是***方案，不推荐使用。

先上代码：(这里拿另一个关系表的真实query举例，原理一样)

 
 
 
   
  
  
  INSERT INTO `user_topic` (`user_id`, `topic_id`)   
  
  
     
  
  
  SELECT :userId, :topicid FROM `user_topic`   
  
  
     
  
  
  WHERE NOT EXISTS (SELECT * FROM `user_topic`   
  
  
     
  
  
  WHERE `user_topic`.`user_id` = :userId   
  
  
     
  
  
  AND `user_topic`.`topic_id` = :topicid)   
  
  
     
  
  
  LIMIT 1;

(相同方法见http://stackoverflow.com/a/31...)

通过这种“插入时判断不存在才插入并返回行数为1，存在的话返回行数为0”的方法，可以做到：

只有在返回行数为1的情况下才执行之后逻辑(如缓存内的统计数+1，缓存内帖子推荐人增加此userId等等)
如果返回行数为0，则接口返回error

解决方案二：清理脏数据并建立联合唯一索引

这个方案是本文的核心了，也是我们目前认为的***实践。

***步：查找user_id, thread_id的联合duplication

 
 
 
   
  
  
  SELECT a.* FROM `thread_recommend` a   
  
  
     
  
  
  INNER JOIN (SELECT * FROM `thread_recommend` GROUP BY `thread_id`, `user_id` HAVING COUNT(id) > 1) b ON a.`thread_id` = b.`thread_id` AND a.`user_id` = b.`user_id`   
  
  
     
  
  
  ORDER BY a.`user_id` ASC, a.`thread_id` ASC, a.`id` DESC

或简单的版本

 
 
 
   
  
  
  SELECT * FROM `thread_recommend`   
  
  
     
  
  
  WHERE (`user_id`, `thread_id`) IN (SELECT `user_id`, `thread_id` FROM `thread_recommend` GROUP BY `user_id`, `thread_id` HAVING COUNT(1) > 1);

得到

哇!所有的重复项都在这里了，好想马上把它们干掉!

现在需要将重复的条目中ID更大的所有条目都删除，只留ID最小的那一个。

删之前先获得需要删除项，比对一下，

 
 
 
   
  
  
  SELECT * FROM `thread_recommend`   
  
  
     
  
  
  WHERE (`user_id`, `thread_id`) IN (SELECT `user_id`, `thread_id` FROM `thread_recommend` GROUP BY `user_id`, `thread_id` HAVING COUNT(1) > 1)   
  
  
     
  
  
  AND `id` NOT IN (SELECT MIN(`id`) FROM `thread_recommend` GROUP BY `user_id`, `thread_id` HAVING COUNT(1) > 1);

下一步，SELECT * FROM改成DELETE FROM，删除!

 
 
 
   
  
  
  DELETE FROM `thread_recommend`   
  
  
     
  
  
  WHERE (`user_id`, `thread_id`) IN (SELECT `user_id`, `thread_id` FROM `thread_recommend` GROUP BY `user_id`, `thread_id` HAVING COUNT(1) > 1)   
  
  
     
  
  
  AND `id` NOT IN (SELECT MIN(`id`) FROM `thread_recommend` GROUP BY `user_id`, `thread_id` HAVING COUNT(1) > 1);

Oops!报错! You can't specify target table 'thread_recommend' for update in FROM clause

这是Mysql的一个小问题，我们参见解决方案 http://stackoverflow.com/a/14... 后修改一下SQL就好：

 
 
 
   
  
  
  DELETE FROM `thread_recommend`   
  
  
     
  
  
  WHERE (`user_id`, `thread_id`) IN (SELECT `user_id`, `thread_id` FROM (SELECT * FROM `thread_recommend`) a GROUP BY `user_id`, `thread_id` HAVING COUNT(1) > 1)   
  
  
     
  
  
  AND `id` NOT IN (SELECT MIN(`id`) FROM (SELECT * FROM `thread_recommend`) b GROUP BY `user_id`, `thread_id` HAVING COUNT(1) > 1);

***，加联合唯一索引!

 
 
 
   
  
  
  ALTER TABLE `thread_recommend`   
  
  
     
  
  
  ADD UNIQUE KEY `thread_id_user_id_unique`(`thread_id`,`user_id`) USING BTREE;

Of course，如果上述清理工作没有完成将会报错!

完!

当前名称：MySQL去除“关联表”重复数据，以建立联合唯一索引
标题路径：http://www.shufengxianlan.com/qtweb/news8/156208.html

网站建设、网络推广公司-创新互联，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源：创新互联

猜你还喜欢下面的内容