登录
首页 >  数据库 >  MySQL

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

来源:SegmentFault

时间:2023-02-24 17:26:19 346浏览 收藏

亲爱的编程学习爱好者,如果你点开了这篇文章,说明你对《MySQL去除“关联表”重复数据,以建立联合唯一索引》很感兴趣。本篇文章就来给大家详细解析一下,主要介绍一下MySQL、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去除“关联表”重复数据,以建立联合唯一索引》的内容介绍就到此结束,如果有什么疑问或者建议,可以在golang学习网公众号下多多回复交流;文中若有不正之处,也希望回复留言以告知!

声明:本文转载于:SegmentFault 如有侵犯,请联系study_golang@163.com删除
相关阅读
更多>
最新阅读
更多>
课程推荐
更多>