修复多对多数据库中重复演员记录的SQL问题排查

问题背景 #

actor表存在重复姓名的记录，该表通过video_actor关联表与video表关联。需要将video_actor中指向重复演员的actor_id统一改为该姓名对应的最小ID（min_id），再删除actor表中的重复记录。PHP循环实现的逻辑可以正常工作，但纯SQL尝试多次失败，需排查问题。

表结构 #

CREATE TABLE `actor` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` text NOT NULL,
 `pic_id` int(11) DEFAULT NULL,
 `dob` varchar(4) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `pic_id` (`pic_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3195 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

CREATE TABLE `video_actor` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `video_id` int(11) NOT NULL,
 `actor_id` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `video_id` (`video_id`),
 KEY `actor_id` (`actor_id`),
 CONSTRAINT `video_actor_ibfk_2` FOREIGN KEY (`actor_id`) REFERENCES `actor` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=23757 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

错误SQL分析 #

第一次尝试的SQL #

UPDATE video_actor as x
join 
(
   SELECT min_id,max_id
      FROM (
   SELECT name, MIN(id) as min_id,MAX(id) as max_id
      FROM actor
   GROUP BY name HAVING COUNT(name) > 1
   ) AS y
) as q on x.video_id = q.min_id
SET x.actor_id = q.min_id
WHERE x.actor_id = q.max_id;

问题：关联条件逻辑错误。x.video_id = q.min_id是将video_actor的视频ID与演员的最小ID关联，这和业务逻辑无关——我们需要的是找到video_actor中actor_id等于重复演员最大ID的记录，而非关联视频ID和演员ID，导致没有匹配的记录，所以更新无效果。

第二次尝试的SQL #

UPDATE video_actor as x
join 
(
   SELECT id,min_id,max_id
      FROM (
         SELECT id, name, MIN(id) as min_id,MAX(id) as max_id
         FROM actor
         GROUP BY name HAVING COUNT(name) > 1
      ) AS y
   ) as q on x.video_id = y.id
SET x.actor_id = q.min_id
WHERE x.actor_id = q.max_id;

问题：

1. 别名引用错误：外层关联时使用了y.id，但y是内层子查询的别名，外层只能引用外层子查询的别名q的字段，无法直接访问y的字段。
2. 同样存在关联条件错误，错误地将video_id和演员ID关联，逻辑不成立。

正确的纯SQL实现 #

第一步：更新video_actor关联表 #

一次性将所有指向重复演员最大ID的记录，改为对应的最小ID：

UPDATE video_actor x
JOIN (
    SELECT name, MIN(id) AS min_id, MAX(id) AS max_id
    FROM actor
    GROUP BY name
    HAVING COUNT(name) > 1
) y ON x.actor_id = y.max_id
SET x.actor_id = y.min_id;

第二步：删除actor表中的重复记录 #

删除所有重复姓名对应的最大ID的演员记录：

DELETE a
FROM actor a
JOIN (
    SELECT name, MAX(id) AS max_id
    FROM actor
    GROUP BY name
    HAVING COUNT(name) > 1
) b ON a.id = b.max_id;

逻辑说明 #

• 更新语句直接关联video_actor中actor_id等于重复演员max_id的记录，批量将其改为对应min_id，和PHP循环的单条UPDATE逻辑完全一致，只是通过JOIN批量完成。
• 删除语句通过关联找到所有重复姓名的max_id，直接删除这些冗余记录。

内容的提问来源于stack exchange，提问作者T' rip