MySQL 8向binary字段插入二进制字符串失败问题排查求助

问题场景 #

执行以下SQL：

CREATE TABLE `tmp` (
  `wid` bigint unsigned NOT NULL,
  `simhash` binary(255) NOT NULL,
  PRIMARY KEY (`wid`),
  UNIQUE KEY `u_simhash` (`simhash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    
TRUNCATE `tmp`;
insert into tmp set wid=4, simhash=b'10';
SELECT wid,bin(simhash+0) FROM tmp;

期望将二进制字符串'10'（对应十进制2）存入simhash字段，但查询bin(simhash+0)返回结果为0，而非预期的'10'。需要存储类似'1000110101101....'的长二进制字符串。

环境：MySQL 8.0.40，客户端MySQL Workbench 8.0.40

问题原因 #

1. 混淆二进制字面量与二进制字符串：MySQL中b'10'是二进制字面量，代表ASCII码为0x10（十进制16）的单个字节，而非字符串形式的二进制数字'10'，存入Binary字段后自然不对应数值2。
2. Binary字段的本质：Binary类型存储的是字节序列，不是文本格式的二进制数字。执行simhash+0时，MySQL会将字段中的字节转换为对应ASCII数值，若字节为不可打印字符，就会出现不符合预期的计算结果。

解决方案 #

方案1：用字符串类型直接存储二进制数字文本（推荐） #

如果需求是存储类似'10'、'10001101...'的文本形式二进制字符串，使用VARCHAR或CHAR类型更适配：

1. 修改表结构：

CREATE TABLE `tmp` (
  `wid` bigint unsigned NOT NULL,
  `simhash` VARCHAR(255) NOT NULL, -- 长度固定可改用CHAR(255)
  PRIMARY KEY (`wid`),
  UNIQUE KEY `u_simhash` (`simhash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

2. 插入数据：

INSERT INTO tmp SET wid=4, simhash='10';

3. 查询数据：

SELECT wid, simhash FROM tmp; -- 直接返回预期的'10'

方案2：将二进制字符串转字节后存入Binary字段 #

若坚持使用Binary类型，需先把二进制字符串转换成对应字节序列：

1. 插入数据（通过函数转义）：

-- 将二进制字符串'10'转换为字节存储
INSERT INTO tmp SET wid=4, simhash=UNHEX(LPAD(CONV('10',2,16), 2, '0'));

2. 查询数据（反向转换还原）：

SELECT wid, BIN(CONV(HEX(simhash),16,2)) FROM tmp; -- 返回'10'

注意：此方法仅适合较短的二进制数值，若SimHash长度超过MySQL数值类型上限（如256位），仍推荐用字符串类型存储。

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