基于关联Exif表数据更新Media表实现LivePhoto关联配置

问题背景 #

需要将照片与视频关联以实现LivePhoto功能，现有Media和Exif两张数据表，已知同名的IMAGE与VIDEO记录为唯一配对（无重复）。

初始数据表状态 #

Media表

Exif表

需求 #

仅当IMAGE与VIDEO记录的imageName相同时，执行以下操作：

• 更新Media表中IMAGE类型记录的livephotoVideoId为对应VIDEO记录的id
• 将该VIDEO记录的visible字段设为FALSE

期望Media表状态 #

Exif表无变化

现有尝试代码 #

update media
set "livephotoVideoId" = (
        select e1.id from exif e1 left join media m2 on m2.id = e1."mediaId"
        where e1."imageName" = (
            select e2."imageName" from exif e2 left join media m3 on m3.id = e2."mediaId" where a3."type" = 'IMAGE'
        ) and a2."type" = 'VIDEO'
    );

正确SQL解决方案 #

方案1：分步关联更新（通用兼容） #

-- 第一步：为IMAGE记录匹配对应的VIDEO ID
UPDATE Media img
SET livephotoVideoId = vid.id
FROM Media vid
JOIN Exif exif_img ON exif_img.mediaId = img.id
JOIN Exif exif_vid ON exif_vid.mediaId = vid.id
WHERE img.type = 'IMAGE'
  AND vid.type = 'VIDEO'
  AND exif_img.imageName = exif_vid.imageName;

-- 第二步：将配对的VIDEO记录设为不可见
UPDATE Media vid
SET visible = FALSE
FROM Media img
JOIN Exif exif_img ON exif_img.mediaId = img.id
JOIN Exif exif_vid ON exif_vid.mediaId = vid.id
WHERE img.type = 'IMAGE'
  AND vid.type = 'VIDEO'
  AND exif_img.imageName = exif_vid.imageName
  AND img.livephotoVideoId = vid.id;

方案2：使用CTE预定义配对关系（支持CTE的数据库如PostgreSQL、MySQL 8+等） #

WITH LivePhotoPairs AS (
    SELECT 
        img.id AS image_id,
        vid.id AS video_id
    FROM Media img
    JOIN Exif exif_img ON exif_img.mediaId = img.id
    JOIN Exif exif_vid ON exif_vid.imageName = exif_img.imageName
    JOIN Media vid ON vid.id = exif_vid.mediaId
    WHERE img.type = 'IMAGE'
      AND vid.type = 'VIDEO'
)
-- 更新IMAGE的关联视频ID
UPDATE Media
SET livephotoVideoId = lpp.video_id
FROM LivePhotoPairs lpp
WHERE Media.id = lpp.image_id;

-- 更新VIDEO的可见状态
UPDATE Media
SET visible = FALSE
FROM LivePhotoPairs lpp
WHERE Media.id = lpp.video_id;

方案说明 #

• 两种方案均通过Exif表的imageName字段匹配IMAGE和VIDEO记录，利用已知的唯一配对规则确保不会出现多匹配错误
• 分步更新或CTE预定义配对的方式，逻辑清晰且易于排查，避免了子查询嵌套导致的逻辑混乱
• 方案2的CTE写法更简洁，适合支持该特性的数据库；方案1兼容性更强，适配多数主流数据库

内容的提问来源于stack exchange，提问作者Peter Bašista