Finding duplicate images in a WordPress media library, without flagging WPML translations

在 WordPress 媒体库中查找重复图片，且不误报 WPML 翻译文件

How I added exact-duplicate detection to a free WordPress plugin, what broke on a 3,099-image multilingual site, and the SQL that fixed it. 我如何为一个免费的 WordPress 插件添加精确重复检测功能，在一个拥有 3,099 张图片的多语言网站上遇到了什么问题，以及我是如何通过 SQL 解决这些问题的。

Last week I shipped duplicate image detection in Filikod, the free alt text audit plugin I maintain. On paper the feature is small: fingerprint every image file, group identical fingerprints, show the groups. Then I ran it on a real client site. 3,099 images, three languages under WPML, a media library migrated twice. Three of my assumptions did not survive the afternoon. This is the story, without the plugin’s source code, because the lessons matter more than my implementation. 上周，我在我维护的免费 Alt 文本审计插件 Filikod 中发布了重复图片检测功能。从理论上讲，这个功能很简单：为每个图片文件生成指纹，将相同的指纹分组，然后显示这些组。随后，我在一个真实的客户网站上运行了它。该网站有 3,099 张图片，使用 WPML 管理三种语言，且媒体库经过了两次迁移。我最初的三个假设在那个下午全部被推翻了。这就是整个故事，我不会展示插件的源代码，因为经验教训比我的实现方式更重要。

Assumption 1: a duplicate has a recognisable name

假设 1：重复文件有可识别的名称

I expected duplicates to look like photo.jpg and photo-1.jpg. Some do. Most do not. On my own site the same dashboard screenshot existed under three names, uploaded in July, August and September by three different people, with three different titles and three different alt texts. Nothing in the WordPress admin connects them. You can scroll the media grid for an hour and never notice. 我原以为重复文件看起来会像 photo.jpg 和 photo-1.jpg。有些确实如此，但大多数不是。在我自己的网站上，同一张仪表板截图以三个不同的名称存在，分别由三个人在 7 月、8 月和 9 月上传，拥有三个不同的标题和三个不同的 Alt 文本。WordPress 后台没有任何机制将它们关联起来。你可以在媒体网格中滚动浏览一小时也无法察觉。

So the identity of an image has to be the content of the file, not its name. A fingerprint of the bytes is enough to catch exact copies, and exact copies are the only ones you can act on with confidence. A resized or re-encoded variant is another file, another decision, another project. I left near-duplicates out on purpose. 因此，图片的身份必须由文件内容决定，而不是名称。字节指纹足以捕捉精确的副本，而精确副本才是你可以放心地进行处理的对象。调整大小或重新编码的变体是另一个文件、另一个决策、另一个项目。我特意排除了近似重复的文件。

One subtlety that cost me an hour: since WordPress 5.3, a large upload is scaled down and the attachment points at the scaled file, while the original stays on disk. Two uploads of the same photo produce two scaled files that are usually identical, but “usually” is not a guarantee, a site that changed image library between the two uploads gets different bytes. Fingerprint the original when WordPress kept one. And keep the fingerprint in sync: the file behind an attachment changes after upload far more often than you think, through scaling, image optimisers, the built-in editor, replacement plugins, thumbnail regeneration. All of them end by saving the attachment metadata, which is the moment to recompute. 有一个细节让我浪费了一个小时：自 WordPress 5.3 起，大文件上传后会被缩放，附件指向的是缩放后的文件，而原始文件仍保留在磁盘上。两次上传同一张照片会产生两个缩放文件，它们通常是相同的，但“通常”并不能保证。如果网站在两次上传之间更换了图像处理库，得到的字节就会不同。当 WordPress 保留原始文件时，请对原始文件进行指纹识别。并保持指纹同步：附件背后的文件在上传后发生变化的频率远超你的想象，这可能是通过缩放、图像优化器、内置编辑器、替换插件或缩略图重新生成导致的。所有这些操作最终都会保存附件元数据，那正是重新计算指纹的时机。

Assumption 2: same file, same problem

假设 2：相同的文件，相同的问题

The first scan of the client site listed several hundred groups. Almost all of them were the same thing: one image, three attachments, one per language. WPML Media Translation, Polylang’s media translation and Bogo all create a separate attachment per language, pointing at the same physical file. Same bytes, same fingerprint, and absolutely not a duplicate to clean up. Delete one of those “copies” and the Spanish version of the page loses its image. 对客户网站的首次扫描列出了几百个组。几乎所有组都是同一种情况：一张图片，三个附件，每种语言一个。WPML Media Translation、Polylang 的媒体翻译和 Bogo 都会为每种语言创建一个单独的附件，指向同一个物理文件。字节相同，指纹相同，但这绝对不是需要清理的重复项。删除其中一个“副本”，该页面的西班牙语版本就会丢失图片。

Worse, the alt text audit had been quietly wrong for months on that site. The French, English and Spanish versions of one product photo carried the same alt text, which is normal for a brand name, and the audit counted them as three “duplicated ALT” errors, dragging the score down for something nobody should fix. 更糟糕的是，该网站的 Alt 文本审计功能已经悄悄地出错了好几个月。同一张产品照片的法语、英语和西班牙语版本带有相同的 Alt 文本（对于品牌名称来说这很正常），而审计程序将它们计为三个“重复的 ALT”错误，导致评分下降，而这根本不需要修复。

The fix is a rule, not a hack: a translation set is one image. Every multilingual plugin keeps its links somewhere queryable, WPML in its own table, Polylang in a hidden taxonomy, Bogo in a post meta, so the whole map of “which attachment belongs to which set” can be loaded in one query per request instead of one call per image. Once you have the map, a group of identical files collapses to one entry per translation set, and a group that shrinks to a single entry is not a group anymore. 修复方法是一个规则，而不是黑客手段：一个翻译集就是一张图片。每个多语言插件都会将链接保存在可查询的地方——WPML 在自己的表中，Polylang 在隐藏的分类法中，Bogo 在文章元数据中——因此，“哪个附件属于哪个集合”的完整映射可以在每个请求中通过一次查询加载，而不是为每张图片调用一次。一旦有了这个映射，一组相同的文件就会合并为每个翻译集一个条目，而缩减为一个条目的组就不再是重复组了。

The same rule applies to the alt audit: an alt is only duplicated when it sits on two genuinely distinct images. Weglot, GTranslate and TranslatePress translate on the fly and never create a second attachment. WPGlobus keeps every language in one post. MultilingualPress runs one site per language. Nothing to do for those, but you have to know it to say so in the documentation. 同样的规则也适用于 Alt 审计：只有当两个真正不同的图片使用相同的 Alt 文本时，才算重复。Weglot、GTranslate 和 TranslatePress 是实时翻译，从不创建第二个附件。WPGlobus 将每种语言保存在一篇文章中。MultilingualPress 为每种语言运行一个站点。这些情况无需处理，但你必须了解它们，以便在文档中说明。

After the fix the same library gave 29 groups and 30 redundant files. Every one of them real: the same substrate photo used on two product pages under two names, a pictogram uploaded twice by two editors, a partner logo that existed four times. That is a list a human can actually work through in twenty minutes. 修复后，同一个媒体库显示出 29 个组和 30 个冗余文件。每一个都是真实的重复：同一张底图在两个产品页面下以两个名称使用，一个象形图被两位编辑上传了两次，一个合作伙伴 Logo 存在了四次。这是一个人类可以在二十分钟内处理完的列表。

Assumption 3: the media library contains files

假设 3：媒体库包含文件

The scan finished with 232 images “still waiting”. They were not waiting. They were attachments whose file no longer existed on disk: leftovers of two migrations, still listed in the media library with a title and an alt text, still referenced in a few pages, rendering as broken images to visitors. Nothing in the WordPress admin surfaces this. The library shows a generic icon and moves on. I turned that into its own tab, with the expected path and the page each attachment is used on. On a site that has been migrated, it may be the most useful list of the three. 扫描结束时，有 232 张图片显示“仍在等待”。它们并不是在等待，而是磁盘上已不存在文件的附件：两次迁移留下的残余，在媒体库中仍列有标题和 Alt 文本，在某些页面中仍被引用，对访客来说显示为损坏的图片。WordPress 后台没有任何地方会显示这一点。媒体库只显示一个通用图标就跳过了。我将其做成了一个独立的标签页，显示预期的路径以及每个附件被使用的页面。对于一个经过迁移的网站来说，这可能是这三个列表中最有用的一个。

A side lesson for anyone auditing media on WPML or Polylang

给所有在 WPML 或 Polylang 上进行媒体审计的人的一个额外教训

Both plugins filter admin queries to the currently selected admin language. My audit was silently skipping two thirds of that library, because the admin bar was set to French, until I told the query to ignore third-party filters: 'suppress_filters' => true. 这两个插件都会根据当前选定的后台语言过滤后台查询。我的审计程序悄悄地跳过了该库的三分之二，因为管理栏被设置为法语，直到我告诉查询忽略第三方过滤器：'suppress_filters' => true。

If you have ever written a media audit, an export, or a cleanup script that runs in the admin of a multilingual site, check your counts against a raw SQL count. Mine were off by 1,838 images. 如果你曾经编写过在多语言网站后台运行的媒体审计、导出或清理脚本，请务必将你的统计结果与原始 SQL 查询结果进行核对。我的统计结果当时偏差了 1,838 张图片。

What it deliberately does not do

它刻意不做什么

The plugin never deletes or merges anything. Rewriting every reference to a file across post content, page builder JSON, custom fields and widgets is where duplicate cleaners earn their one-star reviews, and it is not something I want a plugin to do behind someone’s back. It shows every copy, the page it is attached to, marks the oldest one, and leaves the decision to the person in front of the media library. It also warns at upload time when the file you just added already exists, which is the moment the duplicate is cheapest to avoid. 该插件从不删除或合并任何内容。重写文章内容、页面构建器 JSON、自定义字段和小工具中对文件的每一处引用，正是重复文件清理工具获得一星差评的原因，我不想让插件在用户背后做这种事。它只是显示每个副本、它所附加的页面、标记出最旧的一个，并将决定权留给媒体库前的用户。它还会在上传时发出警告，提示你刚刚添加的文件是否已经存在，这是避免重复成本最低的时机。

Numbers from that first afternoon

那天下午的统计数据

• Images in the library: 3,099
• 媒体库中的图片：3,099
• Fingerprinted: 2,867
• 已生成指纹：2,867
• Attachments without a file on disk: 232
• 磁盘上无文件的附件：232
• Groups before handling translations: several hundred
• 处理翻译前的组数：几百个
• Real duplicate groups after: 29
• 处理后的真实重复组数：29