mysql - SQL 字符串文字十六进制键到二进制和返回

Question

经过广泛搜索后，我求助于堆栈溢出的智慧来帮助我。问题:我有一个数据库表，它应该有效地存储 (UserKey, data0, data1, ..) 格式的值，其中 UserKey 将作为主键处理，但至少作为索引。 UserKey 本身(外部定义)是一个 32 个字符的字符串，表示校验和，它恰好是(一个非常大的)十六进制数，即它看起来像这样 UserKey = "000000003abc4f6e000000003abc4f6e"。

现在我当然可以将这个 UserKey 存储在一个 char(32) 字段中，但我觉得这非常低效，因为我存储了一系列原则上的任意字符，即为每个字符保留比 4 个字符更多的信息空间我需要存储十六进制字符 (0..9,A-F) 的位。

所以我的想法是将这个字符串文字转换成它真正代表的十六进制数字，并存储它。但是这个数字(32*4 位 = 16 字节)对于存储/处理来说太大了，因为 SQL 只能处理 8 字节的 BIGINTS。

我的第二个想法是将其转换为 BINARY(16) 表示形式，这在内存方面应该是紧凑且高效的。但是，我不知道如何在这两种格式之间进行有效转换，因为 SQL 在内部也只能处理最大 8 字节的数字。

也许有一种方法可以将这个字符串逐 block 转换为二进制，然后以某种方式将二进制拼接在一起，方法是:

 UserKey == concat( stringblock1, stringblock2, ..)
 UserKey_binary = concat( toBinary( stringblock1 ), toBinary( stringblock2 ), ..)

所以我的问题是:SQL 中是否有任何可预见的机制可以为我解决这个问题？自定义解决方案会是什么样子？ (我很难相信我应该是第一个遇到这种问题的人，因为在许多应用程序中使用长得可笑的散列键已经变得非常现代)

此外，Userkey_binary 应该充当表的关系键，所以我希望通过这种更紧凑的表示来提高一点速度，因为它需要确定最少数量的差异位。此外，我想提一下，如果可能的话，我想在服务器端进行任何转换，这样用户脚本就不会被改变(如果可能的话，用户端应该仍然传输一个字符串文字而不是[部分] 插入语句中的转换值)

Answer 1

与我之前的陈述相矛盾的是，MySQL 的 UNHEX() 函数似乎逐 block 地从字符串进行转换，然后像我上面所说的那样进行连接，因此该方法也适用于 HEX大于 BIGINT 的 8 字节限制的文字值。这里有一个示例表来说明这一点:

CREATE TABLE `testdb`.`tab` (
`hexcol_binary` BINARY(16) GENERATED ALWAYS AS (UNHEX(charcol)) STORED,
`charcol` CHAR(32) NOT NULL,
PRIMARY KEY (`hexcol_binary`));

主键是生成的列，因此对 charcol 的更新是从外部使用字符串文字与表交互的指定方式:

REPLACE into tab (charcol) VALUES ('1010202030304040A0A0B0B0C0C0D0D0');
SELECT HEX(hexcol_binary) as HEXstring, tab.* FROM tab;

如所见，在 hexcol_binary 上构建键和索引按预期工作。

为了验证加速，采取

ALTER TABLE `testdb`.`tab` 
ADD INDEX `charkey` (`charcol` ASC);
EXPLAIN SELECT * from tab where  hexcol_binary = UNHEX('1010202030304040A0A0B0B0C0C0D0D0') #keylength 16
EXPLAIN SELECT * from tab where charcol = '1010202030304040A0A0B0B0C0C0D0D0' #keylength 97

在 hexcol_binary 列上的查找性能要好得多，特别是如果它另外设置为唯一的话。

注意:十六进制转换并不关心转换过程中十六进制字符 A 到 F 是否大写，但是 charcol 对此非常敏感。