MySQL中SHA()函数对不同长度BINARY类型同值返回不同结果的原因及解决方法

问题场景 #

1. 创建包含固定长度binary字段的数据表：

CREATE TABLE TEST_binary10_2 ( 
  PK bigint NOT NULL, 
  VERSION int NOT NULL, 
  X1 binary(1) DEFAULT NULL, 
  X2 binary(10) DEFAULT NULL,  
  PRIMARY KEY (`PK`)
);

2. 插入数据时给X1、X2字段均赋值为3：

insert into TEST_binary10_2 values(2, 1, 3, 3);

3. 查询数据及字段SHA哈希值，发现结果不一致：

SELECT PK, X1, X2, SHA(X1), SHA(X2) FROM TEST_binary10_2;

原因分析 #

这是MySQL固定长度binary类型的特性导致的：

• 插入整数3到binary字段时，MySQL会将其转换为单字节二进制值0x03。
• binary(N)属于固定长度字段，当存储内容长度不足N时，会自动用NULL字节（0x00）填充至指定长度。
• X1（binary(1)）实际存储的是1字节的0x03，而X2（binary(10)）实际存储的是0x03后跟随9个0x00（共10字节）。两者二进制内容完全不同，SHA哈希计算结果自然存在差异。

解决方法 #

根据业务需求，可选择以下两种方案：

1. 改用可变长度的varbinary类型
   varbinary(N)是可变长度字段，不会自动补0，插入相同二进制值时，存储内容完全一致，哈希结果也会相同。修改建表语句如下：

   CREATE TABLE TEST_binary10_2 ( 
     PK bigint NOT NULL, 
     VERSION int NOT NULL, 
     X1 varbinary(1) DEFAULT NULL, 
     X2 varbinary(10) DEFAULT NULL,  
     PRIMARY KEY (`PK`)
   );
   

2. 手动保证插入内容的长度匹配
   如果必须使用binary类型，插入时需手动将内容填充到对应字段的指定长度，比如给X2插入补0后的匹配内容：

   insert into TEST_binary10_2 values(2, 1, 3, LPAD(3, 10, CHAR(0)));
   

   此时X1的0x03与X2的0x03+9个0x00哈希值会一致（适用于业务需要补0后匹配的场景）。

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