文件元数据表双候选唯一键的存储结构与实现方案咨询

你提到的COALESCE(md5, CONCAT(filename, filesize))计算列方案完全可行，是这个场景下性价比最高的选择，只需要做两个小优化就能落地：

• 拼接filename和filesize时必须加特殊分隔符：直接拼接会出现极端冲突，比如文件名report1024大小2048，和文件名report大小10242048拼接结果完全一致，改成CONCAT(filename, '||', filesize)即可，选一个你的业务里绝对不会出现在文件名里的字符做分隔符就行。
• 优先用数据库原生的存储生成列（STORED Generated Column）实现，不要用应用层生成或者触发器：应用层生成容易出现多业务端逻辑不一致、代码迭代漏处理的问题，触发器属于隐性逻辑后期排查成本高，主流MySQL 5.7+、PostgreSQL都支持原生生成列，逻辑直接写在表定义里由数据库自动维护，性能和一致性都有保障。

给一个MySQL的参考表定义：

CREATE TABLE file_metadata (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    filename VARCHAR(255) NOT NULL COMMENT '文件名',
    filesize BIGINT UNSIGNED NOT NULL COMMENT '文件大小（字节）',
    lastmodified DATETIME NOT NULL COMMENT '最后修改时间',
    md5 CHAR(32) DEFAULT NULL COMMENT '文件MD5值',
    -- 统一唯一键生成逻辑，存储生成列
    uniq_key VARCHAR(290) GENERATED ALWAYS AS (
        COALESCE(md5, CONCAT(filename, '||', filesize))
    ) STORED NOT NULL,
    UNIQUE KEY uk_uniq_key (uniq_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

如果你的数据量超过千万级，可以把uniq_key替换成哈希值存储，比如用CRC32(COALESCE(md5, CONCAT(filename, '||', filesize)))生成32位整数存，索引体积会小很多，查询速度更快，冲突概率也几乎可以忽略。

这类优先用高置信度唯一键、缺失时回退用组合键的场景，业内通用的落地原则有几个：

• 绝对不要用复合唯一键实现：很多人第一反应是建UNIQUE KEY (md5, filename, filesize)，但SQL标准里NULL和任何值都不相等，两条md5为NULL、其余字段完全相同的数据可以正常重复插入，完全达不到约束效果。
• 统一唯一键逻辑收敛到数据库层：能不用应用层生成就不用，能不用触发器就不用，用原生生成列把逻辑写死在表结构里，避免后续多人维护、多端写入导致的逻辑不一致。
• 所有字符串拼接必须加隔离分隔符：只要是多字段拼接生成唯一键的场景，都要加业务不可能出现的分隔符，彻底避免不同字段组合拼接出相同字符串的极端情况。
• 可加兜底校验逻辑：如果业务对那0.1%的误判零容忍，可以在捕获到唯一键冲突报错时，额外把冲突行的全量字段拉出来和待写入数据做二次比对，确认是真重复还是极小概率的碰撞，再走后续的覆盖/报错逻辑。

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