MySQL如何查询文件名追加数字后缀的重复记录

不要用固定横杠数量、固定后缀长度的LIKE规则匹配，这类写法无法兼容文件名本身带横杠的场景，误判率极高。
核心匹配逻辑只需要卡两个判定条件，就能100%精准识别目标重复记录：

• 目标记录文件名中，最后一个横杠（-）到文件扩展名前的内容为纯数字
• 去掉「横杠+纯数字后缀」之后得到的原始文件名，真实存在于当前表中

以下是通用查询代码，假设使用的表名为your_table，存储文件名的字段为filename，默认适配带扩展名的文件场景：

SELECT DISTINCT dup.*
FROM your_table dup
INNER JOIN your_table origin
  -- 关联匹配：重复记录去掉-数字后缀后，与原始记录文件名完全一致
  ON origin.filename = CONCAT(
    -- 截取最后一个横杠之前的文件名主体部分
    SUBSTRING(
      dup.filename,
      1,
      LENGTH(dup.filename) - LENGTH(SUBSTRING_INDEX(dup.filename, '-', -1)) - 1
    ),
    -- 拼接原文件扩展名
    '.',
    SUBSTRING_INDEX(dup.filename, '.', -1)
  )
WHERE
  -- 基础过滤：文件名必须包含横杠和扩展名
  dup.filename LIKE '%-%.%'
  -- 校验最后一个横杠到扩展名之间的内容为纯数字
  AND (
    SUBSTRING_INDEX(SUBSTRING_INDEX(dup.filename, '.', 1), '-', -1) 
    REGEXP '^[0-9]+$'
  )
  -- 排除记录自关联的情况（如果表有主键id就用id判断，没有可以替换为dup.filename != origin.filename）
  AND dup.id != origin.id;

适配调整说明 #

• 如果是MySQL 8.0以上版本，可以把REGEXP写法替换为REGEXP_LIKE(匹配内容, '^[0-9]+$')，效果完全一致
• 如果存储的文件名不带扩展名，直接去掉关联条件中拼接扩展名的部分，同时调整纯数字校验逻辑为「取最后一个横杠之后的所有内容判断为纯数字」即可
• 如果需要直接批量修正重复数据，把SELECT DISTINCT dup.*改为UPDATE语法即可，执行前务必备份全表数据

避坑提示：不要用LIKE '%-[0-9]%.%'这类简单正则匹配，会把文件名本身带横杠加数字的正常记录误判为重复项，比如annual-report-2024.pdf这类自带年份的文件，只有当表中真实存在annual-report.pdf这条原始记录时，对应的后缀带数字的条目才会被识别为重复项，不会出现误判。

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