代码之家 › 专栏 › 技术社区 › jeremy Gavin Towey

如何计算MySQL查询中两个哈希之间的差异?

phash hamming-distance bit-manipulation hash mysql

jeremy Gavin Towey · 技术社区 · 7 年前

我试图计算输入哈希和数据库存储哈希之间的汉明距离。这些是感知散列,所以它们之间的汉明距离对我来说很重要,告诉我两个不同的图像有多相似(参见 http://en.wikipedia.org/wiki/Perceptual_hashing http://jenssegers.com/61/perceptual-image-hashes , http://stackoverflow.com/questions/21037578/ ).Hash是16个十六进制字符,如下所示:

b1d0c44a4eb5b5a9

751a0b19f0c2783f

我的数据库如下所示:

CREATE TABLE `hashes` (
  `id` int(11) NOT NULL,
  `hash` binary(8) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

INSERT INTO `hashes` (`id`, `hash`) VALUES
    (1, 0xb1d0c44a4eb5b5a9),
    (2, 0x1f69f25228ed4a31),
    (3, 0x751a0b19f0c2783f);

现在,我知道我可以像这样查询汉明距离:

SELECT BIT_COUNT(0xb1d0c44a4eb5b5a9 ^ 0x751a0b19f0c2783f)

SELECT BIT_COUNT(hash ^ 0x751a0b19f0c2783f) FROM hashes

有人知道我怎么能像第一次那样计算汉明距离吗 SELECT 使用我的数据库中的列进行上述查询?我已经尝试过无数种使用 hex() , unhex() , conv() cast() 以不同的方式。这是MySQL。

使现代化 当在MySQL v8中运行时,我上面的查询似乎可以正常工作(感谢@LukStorms指出这一点)。您可以使用下面的我的小提琴,并更改左上角的版本。我现在的问题是:如何确保该行为在所有版本的MySQL中都有效?

小提琴: https://www.db-fiddle.com/f/mpqsUpZ1sv2kmvRwJrK5xL/0

3 回复 | 直到 7 年前

Nick SamSmith1986 7 年前

CREATE TABLE `hashes` (
  `id` int(11) NOT NULL,
  `hash` bigint unsigned NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

INSERT INTO `hashes` (`id`, `hash`) VALUES
    (1, 0xb1d0c44a4eb5b5a9),
    (2, 0x1f69f25228ed4a31),
    (3, 0x751a0b19f0c2783f);

SELECT id, HEX(hash), BIT_COUNT(hash ^ 0x751a0b19f0c2783f)
FROM hashes;

输出:

id  HEX(hash)           BIT_COUNT(hash ^ 0x751a0b19f0c2783f)
1   B1D0C44A4EB5B5A9    38
2   1F69F25228ED4A31    34
3   751A0B19F0C2783F    0

Demo on dbfiddle

通过以下查询可以看出MySQL 5.7和8.0在使用字符串类型方面的区别:

SELECT id, hash, HEX(hash), HEX(hash ^ 0x751a0b19f0c2783f)
FROM hashes;

MySQL 5.7:

id  hash                                                        HEX(hash)           HEX(hash ^ 0x751a0b19f0c2783f)
1   {"type":"Buffer","data":[177,208,196,74,78,181,181,169]}    B1D0C44A4EB5B5A9    751A0B19F0C2783F
2   {"type":"Buffer","data":[31,105,242,82,40,237,74,49]}       1F69F25228ED4A31    751A0B19F0C2783F
3   {"type":"Buffer","data":[117,26,11,25,240,194,120,63]}      751A0B19F0C2783F    751A0B19F0C2783F

MySQL 8.0

id  hash                                                        HEX(hash)           HEX(hash ^ 0x751a0b19f0c2783f)
1   {"type":"Buffer","data":[177,208,196,74,78,181,181,169]}    B1D0C44A4EB5B5A9    C4CACF53BE77CD96
2   {"type":"Buffer","data":[31,105,242,82,40,237,74,49]}       1F69F25228ED4A31    6A73F94BD82F320E
3   {"type":"Buffer","data":[117,26,11,25,240,194,120,63]}      751A0B19F0C2783F    0000000000000000

MySQL 8.0正在正确执行XOR,返回一个变量,而MySQL 5.7正在返回被XOR’ed的值,这表明它正在处理 BINARY

Wiimm 7 年前

`hash` binary(8) NOT NULL

请改用bigint:

`hash` bigint unsigned NOT NULL

Jonas Tuemand Møller 7 年前

SELECT id, HEX(hash), CAST(CONV(HEX(hash),16,10) AS UNSIGNED), BIT_COUNT(CAST(CONV(HEX(hash),16,10) AS UNSIGNED) ^ 0x751a0b19f0c2783f) FROM hashes;