MySQL建表及插入数据时如何生成存储多字段哈希值

需要创建一张带哈希列的表，哈希列计算规则为：对非唯一id字段、word字段、标识datetime类型valid字段是否在有效期内的布尔值三者做联合哈希，要求实现整型、字符串、布尔值的混合哈希计算，且仅当对应哈希值不存在时才存入表中。

目前已经拆分出单个功能的写法，但无法整合为可正常运行的完整逻辑：

• 基础哈希计算写法：

SELECT crc32(concat(id, word, boolean)) FROM testhash;

• 日期有效性校验写法：

SELECT id FROM testhash WHERE valid > NOW();

• 哈希重复时跳过插入的写法：

INSERT IGNORE INTO testhash (word,valid,hash) VALUES ('testword', STR_TO_DATE("2022-07-10", "%Y-%m-%d"), 'hereisthehash');

原有建表语句执行失败，语句如下：

CREATE TABLE testhash (
  id int(11) NOT NULL AUTO_INCREMENT,
  word varchar(150) NOT NULL,
  valid datetime NOT NULL DEFAULT '0000-00-00',
  hash varchar(64) AS (crc32(CONCAT(id, word))) STORED NOT NULL UNIQUE KEY,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

原建表语句无法运行的核心问题有三个：

1. 生成列依赖自增字段的时序冲突：id是AUTO_INCREMENT字段，MySQL中自增值是在生成列计算完成后才正式分配的，不支持存储生成列直接引用自增字段。
2. 哈希逻辑缺项：原生成列仅拼接了id和word，没有加入valid字段是否在有效期内的布尔判断，不符合需求。
3. 类型不匹配：crc32()函数返回值为32位无符号整型，原语句将hash定义为varchar(64)类型会触发隐式类型转换，浪费存储空间且可能出现异常。

方案1：插入时手动计算哈希（逻辑直观，无额外依赖） #

首先修正建表语句，将哈希字段设为普通字段加唯一键约束，利用唯一键特性自动拦截重复哈希的记录：

CREATE TABLE testhash (
  id int(11) NOT NULL AUTO_INCREMENT,
  word varchar(150) NOT NULL,
  valid datetime NOT NULL DEFAULT '0000-00-00',
  is_valid TINYINT(1) NOT NULL COMMENT '有效期标识：1=在有效期内 0=已过期',
  hash CHAR(32) NOT NULL COMMENT 'MD5联合哈希值',
  PRIMARY KEY (id),
  UNIQUE KEY uk_hash (hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

插入时直接整合所有计算逻辑，无需提前查询处理：

INSERT IGNORE INTO testhash (word, valid, is_valid, hash)
VALUES (
  'testword',
  '2022-07-10',
  -- 计算有效期布尔值
  IF('2022-07-10' > NOW(), 1, 0),
  -- 计算联合哈希，用固定分隔符拼接避免字段值粘连导致的哈希碰撞
  MD5(CONCAT_WS(
    '||',
    (SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='testhash'),
    'testword',
    IF('2022-07-10' > NOW(), 1, 0)
  ))
);

方案2：用触发器自动计算哈希（使用更简便） #

如果不想每次插入都写冗长的哈希计算逻辑，可以创建BEFORE INSERT触发器，插入时自动补全is_valid和hash字段的值：

1. 建表语句和方案1一致
2. 创建自动计算字段的触发器：

DELIMITER //
CREATE TRIGGER trg_testhash_calc BEFORE INSERT ON testhash
FOR EACH ROW
BEGIN
  -- 自动计算有效期标识
  SET NEW.is_valid = IF(NEW.valid > NOW(), 1, 0);
  -- 自动计算联合哈希
  SET NEW.hash = MD5(CONCAT_WS(
    '||',
    (SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='testhash'),
    NEW.word,
    NEW.is_valid
  ));
END //
DELIMITER ;

配置完成后，插入语句可以简化为：

INSERT IGNORE INTO testhash (word, valid) VALUES ('testword', '2022-07-10');

触发器会自动完成所有字段计算，遇到哈希重复的记录会自动跳过插入，完全满足需求。

注意事项 #

• 不推荐用crc32()做业务去重哈希，32位校验值碰撞概率极高，推荐使用MD5()（128位）或SHA1()（160位），碰撞概率可忽略。
• 拼接哈希原值时必须用CONCAT_WS()加固定特殊分隔符，避免出现不同字段组合拼接后字符串完全一致的问题，比如id=12,word='3a'和id=1,word='23a'直接用CONCAT()拼接结果完全相同，会导致哈希错误重复。

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