- 当前位置：首页 >人工智能 >MySQL去除“关联表”重复数据，以建立联合唯一索引
MySQL去除“关联表”重复数据，以建立联合唯一索引
发布时间：2025-11-05 04:02:32 来源：云智核 作者：系统运维
前言
昨天遇到一个问题，关联表需要对一张关系表进行重构和优化。除重然而这张关系表由于已有代码没有注重并发导致了很多的复数脏数据，即重复数据。据建
表名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 cant 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，如果上述清理工作没有完成将会报错!
完!
IT技术网- Copyright © 2025 Powered by MySQL去除“关联表”重复数据，以建立联合唯一索引,云智核 滇ICP备2023006006号-28sitemap