MySQL四表关联查询返回重复数据，如何获取tb_images._id唯一记录？

问题根源分析 #

你遇到的重复记录问题，本质是多表JOIN时产生了笛卡尔积：当tb_images的某条记录在关联表（tb_photo_outlets/tb_techniques/tb_photographers）中匹配到多条记录时，JOIN操作会把这些组合全部返回，即便用DISTINCT也无法消除——因为关联表的字段值不同，导致整行记录不重复。

排查步骤 #

先定位是哪个关联表导致的一对多匹配：

-- 检查tb_photo_outlets是否存在一对多关联
SELECT i._id, COUNT(o._id) AS match_count
FROM tb_images i
LEFT JOIN tb_photo_outlets o ON i._source = o._id
WHERE i._id IN (47416,86084,107283,118473,29571,9408,86088)
GROUP BY i._id
HAVING match_count > 1;

-- 检查tb_techniques是否存在一对多关联
SELECT i._id, COUNT(t._abbrev) AS match_count
FROM tb_images i
LEFT JOIN tb_techniques t ON i._phototype_abbrev = t._abbrev
WHERE i._id IN (47416,86084,107283,118473,29571,9408,86088)
GROUP BY i._id
HAVING match_count > 1;

-- 检查tb_photographers是否存在一对多关联
SELECT i._id, COUNT(p._code) AS match_count
FROM tb_images i
LEFT JOIN tb_photographers p ON i._photographer = p._code
WHERE i._id IN (47416,86084,107283,118473,29571,9408,86088)
GROUP BY i._id
HAVING match_count > 1;

返回结果的查询，对应的关联表就是重复记录的来源。

解决方案 #

方案1：子查询获取单条关联记录 #

对每个关联字段使用LIMIT 1的子查询，确保每条tb_images记录只匹配一条关联表数据：

SELECT
    i._id, i._photographer, i._attributed, i._ignore_photographer,
    i._origtitle, i._origdate, 
    i._series,i._show_desc, 
    i._desc, i._phototype_abbrev, 
    i._phototype, i._size,i._sitter, 
    -- 取关联表的单条记录
    (SELECT o._name FROM tb_photo_outlets o WHERE o._id = i._source LIMIT 1) AS outlet_name,
    (SELECT t._abbrev FROM tb_techniques t WHERE t._abbrev = i._phototype_abbrev LIMIT 1) AS technique_abbrev,
    (SELECT CONCAT(p._fn, ' ', p._sn) FROM tb_photographers p WHERE p._code = i._photographer LIMIT 1) AS photographer_name
FROM tb_images i
WHERE i._id IN (47416,86084,107283,118473,29571,9408,86088)
ORDER BY FIELD(i._id,47416,86084,107283,118473,29571,9408,86088);

方案2：GROUP BY聚合关联字段 #

通过GROUP BY tb_images._id，对关联表字段使用MAX()/MIN()等聚合函数，取任意一条有效记录（需确保非聚合字段都包含在GROUP BY中，适配MySQL的ONLY_FULL_GROUP_BY模式）：

SELECT
    i._id, i._photographer, i._attributed, i._ignore_photographer,
    i._origtitle, i._origdate, 
    i._series,i._show_desc, 
    i._desc, i._phototype_abbrev, 
    i._phototype, i._size,i._sitter, 
    MAX(o._name) AS outlet_name,
    MAX(t._abbrev) AS technique_abbrev,
    MAX(CONCAT(p._fn, ' ', p._sn)) AS photographer_name
FROM tb_images i
LEFT JOIN tb_photo_outlets o ON i._source = o._id
LEFT JOIN tb_techniques t ON i._phototype_abbrev = t._abbrev
LEFT JOIN tb_photographers p ON i._photographer = p._code
WHERE i._id IN (47416,86084,107283,118473,29571,9408,86088)
GROUP BY 
    i._id, i._photographer, i._attributed, i._ignore_photographer,
    i._origtitle, i._origdate, 
    i._series,i._show_desc, 
    i._desc, i._phototype_abbrev, 
    i._phototype, i._size,i._sitter
ORDER BY FIELD(i._id,47416,86084,107283,118473,29571,9408,86088);

方案3：清理冗余数据（根源解决） #

如果关联表的多条匹配记录是冗余的，直接清理数据并添加唯一约束：
比如给tb_photo_outlets._id加唯一约束，确保每个_id只对应一条记录：

ALTER TABLE tb_photo_outlets ADD UNIQUE KEY unique_id (_id);

内容的提问来源于stack exchange，提问作者foxtalbot