用你目前的结构做这件事是非常低效的，但可以做到。
需要注意的是，由于duplicates表是一个连接表，因此应该使用复合索引在两个方向上对它进行索引：

CREATE TABLE `duplicates` (
    `original` INT NOT NULL,
    `additional` INT NOT NULL,
    PRIMARY KEY (`original`, `additional`),
    INDEX (`additional`, `original`),
    FOREIGN KEY (`original`) REFERENCES `pics` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (`additional`) REFERENCES `pics` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
);

根据一组标签搜索pic ID：

SELECT COALESCE(d.original, t.id) AS id
FROM tags t
LEFT JOIN duplicates d ON d.additional = t.id
WHERE t.tag IN ('forest', 'brown', 'vines')
GROUP BY 1
HAVING COUNT(DISTINCT t.tag) = 3 -- because we have 3 tags to find

搜索给定pic ID的源：

SELECT p.sourceSite
FROM pics p
WHERE p.id = 2
UNION
SELECT p.sourceSite
FROM pics p
JOIN duplicates d ON d.additional = p.id
WHERE d.original = 2;

如果你想把上面两个联系在一起，这样你就可以根据一组标签搜索图片，并返回ID，来源和标签的完整列表，你可以做以下事情。

第一步：获取图像的ID和所有需要的标签：

WITH pics_by_tags (pic_id) AS (
    SELECT COALESCE(d.original, t.id)
    FROM tags t
    LEFT JOIN duplicates d ON d.additional = t.id
    WHERE t.tag IN ('forest', 'brown', 'vines')
    GROUP BY 1
    HAVING COUNT(DISTINCT t.tag) = 3 -- because we have 3 tags to find
)
SELECT * FROM pics_by_tags;

输出量：
| pic_id|
| --|
| 2 |

第二步：获取上一步返回的每个图片的源代码：

WITH pics_by_tags (pic_id) AS (
    /* as before */
),
pics_with_sites (pic_id, sites) AS (
    SELECT pbt.pic_id, GROUP_CONCAT(DISTINCT p.sourceSite)
    FROM pics_by_tags pbt
    JOIN pics p
        ON pbt.pic_id = p.id
        OR EXISTS (SELECT 1 FROM duplicates d WHERE pbt.pic_id = d.original AND p.ID = d.additional)
    GROUP BY 1
)
SELECT * FROM pics_with_sites;

输出量：
| pic_id|网站|
| --|--|
| 2 |网址：colors.com，nature.com|

第三步：获取上一步结果的完整标签列表：

WITH pics_by_tags (pic_id) AS (
    /* as before */
),
pics_with_sites (pic_id, sites) AS (
    /* as before */
)
SELECT p.pic_id, p.sites, GROUP_CONCAT(DISTINCT t.tag ORDER BY t.tag) AS tags
FROM pics_with_sites p
JOIN tags t
    ON p.pic_id = t.id
    OR EXISTS (SELECT 1 FROM duplicates d WHERE p.pic_id = d.original AND t.id = d.additional)
GROUP BY p.pic_id, p.sites;

输出量：
| pic_id|网站|标签|
| --|--|--|
| 2 |网址：colors.com，nature.com|棕色，森林，绿色，树，藤蔓|
完整的查询是：

WITH pics_by_tags (pic_id) AS (
    SELECT COALESCE(d.original, t.id)
    FROM tags t
    LEFT JOIN duplicates d ON d.additional = t.id
    WHERE t.tag IN ('forest', 'brown', 'vines')
    GROUP BY 1
    HAVING COUNT(DISTINCT t.tag) = 3 -- because we have 3 tags to find
),
pics_with_sites (pic_id, sites) AS (
    SELECT pbt.pic_id, GROUP_CONCAT(DISTINCT p.sourceSite)
    FROM pics_by_tags pbt
    JOIN pics p
        ON pbt.pic_id = p.ID
        OR EXISTS (SELECT 1 FROM duplicates d WHERE pbt.pic_id = d.original AND p.id = d.additional)
    GROUP BY 1
)
SELECT p.pic_id, p.sites, GROUP_CONCAT(DISTINCT t.tag ORDER BY t.tag) AS tags
FROM pics_with_sites p
JOIN tags t
    ON p.pic_id = t.id
    OR EXISTS (SELECT 1 FROM duplicates d WHERE p.pic_id = d.original AND t.id = d.additional)
GROUP BY p.pic_id, p.sites;

显然，一个没有重复的规范化结构将更好地完成这项任务。
例如：

pictures (id, other_picture_meta_data)
sources (id, name)
tags_new (id, name)
pics_sources (pic_id, source_id)
pics_tags (pic_id, tag_id)

使用修改后的结构，查询变为：

WITH pics_by_tags (pic_id) AS (
    SELECT pt.pic_id
    FROM tags_new tn
    JOIN pics_tags pt ON tn.id = pt.tag_id
    WHERE tn.name IN ('forest', 'brown', 'vines')
    GROUP BY 1
    HAVING COUNT(tn.name) = 3 -- because we have 3 tags to find
),
pics_with_sites (pic_id, sites) AS (
    SELECT pbt.pic_id, GROUP_CONCAT(s.name)
    FROM pics_by_tags pbt
    JOIN pics_sources ps ON pbt.pic_id = ps.pic_id
    JOIN sources s ON ps.source_id = s.id
    GROUP BY 1
)
SELECT p.*, pws.sites, GROUP_CONCAT(tn.name ORDER BY tn.name) AS tags
FROM pics_with_sites pws
JOIN pictures p ON pws.pic_id = p.id
JOIN pics_tags pt ON pws.pic_id = pt.pic_id
JOIN tags_new tn ON pt.tag_id = tn.id
GROUP BY p.id;

这里有一个db<>fiddle玩。

下面是您的模式的图形表示。让我们一起努力。

1. SQL查询列出给定标签下的图像：

SELECT pics.*
FROM tags
    LEFT JOIN duplicates ON tags.id = duplicates.original OR tags.id = duplicates.additional
    LEFT JOIN pics on duplicates.original = pics.id
WHERE tags.tag = "green"
GROUP BY duplicates.original;

也就是说，给定输入标签，例如，green：
1.查询tags.tag等于"green"的标签，
1.找出所有与tags.id相关的duplicates，
1.通过按duplicates.original分组将这些副本“压缩”为一个，以便只有一个副本“占上风”，然后
1.根据上面的结果得到相应的pics.*。
事后看来，您可能需要考虑在表tags和duplicates之间创建外键约束。
1.用于列出图像源站点的SQL查询：

SELECT pics.sourceSite
FROM duplicates
    LEFT JOIN pics ON duplicates.additional = pics.id
WHERE duplicates.original = 2 OR duplicates.additional = 2
GROUP BY pics.sourceSite

也就是说，给定输入图像ID，例如2：
1.查找引用图像ID 2的duplicates，
1.查询pics关联到以上结果中的任何重复项，则
1.分组并选择这些图片的源站点。
1.理想情况下，您希望删除重复项并规范化标记。但在现实中，可能存在不这样做的有效因素。简而言之，“这取决于”。

考虑在包含匹配图像的所有标记的列上使用FULLTEXT索引。
然后这将捕获#5和#6，由于更多的“相关性”，首先订购#5：

MATCH(tags) AGAINST ("forest tree" IN BOOLEAN MODE)

而这只会抓住#5：

MATCH(tags) AGAINST ("+forest +tree" IN BOOLEAN MODE)