Как я могу рассчитать разницу между двумя хэшами в запросе MySQL?

Я пытаюсь рассчитать расстояние Хэмминга между входным хешем и хешами, хранящимися в базе данных. Это перцептивные хэши, поэтому расстояние Хемминга между ними важно для меня и говорит мне, насколько похожи два разных изображения (см. http://en.wikipedia.org/wiki/Perceptual_hashing, http://jenssegers.com/61/perceptual-image-hashes, http://stackoverflow.com/questions/21037578/< /а>). Хеши состоят из 16 шестнадцатеричных символов и выглядят следующим образом:

b1d0c44a4eb5b5a9
1f69f25228ed4a31
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)

Который выведет 38, как и ожидалось. Однако я не могу сослаться на имя столбца для этого сравнения. Следующее не работает должным образом.

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< /а>


mysql hash hamming-distance bit-manipulation phash
person jeremy    schedule 02.02.2019    source источник
comment
BIT_COUNT не вычисляет расстояние Хэмминга, а просто подсчитывает установленные биты.   -  person Raymond Nijland    schedule 02.02.2019
comment
@RaymondNijland, вот почему я также выполняю XOR. XOR -> count set bets, по сути, является расстоянием между хемами, если хэши являются двоичными. это работает в моем первом запросе выбора   -  person jeremy    schedule 02.02.2019
comment
@AndrewMorton Это перцептивные хэши, поэтому Хэмминг очень важен для меня, если я пытаюсь запросить дубликаты/похожие изображения. См.: en.wikipedia.org/wiki/Perceptual_hashing jenssegers.com/61/perceptual-image-hashes stackoverflow.com/questions/21037578/   -  person jeremy    schedule 03.02.2019
comment
@AndrewMorton мне тоже очень интересно. я обновил свой вопрос   -  person jeremy    schedule 03.02.2019
comment
@LukStorms Если они равны, ожидаемое расстояние Хэмминга фактически равно нулю (без разницы). Тем не менее, не все расстояния Хэмминга верны.   -  person jeremy    schedule 03.02.2019
comment
Интересно, что эта db‹›fiddle здесь не вроде нет проблем с этим. Ошибка, которую они исправили в MySql 8?   -  person LukStorms    schedule 03.02.2019
comment
@LukStorms это очень интересно. интересно, это баг? скрипка, которую я разместил, также позволяет вам переключить ее на mysql8, где она также работает   -  person jeremy    schedule 03.02.2019

Ответы (3)


arrow_upward
4
arrow_downward

Проблема, похоже, связана с вашим выбором типа данных, который является строковым типом. Использование числового типа данных работает как в MySQL 5.7, так и в 8.0:

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

Демо на 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, указывая, что он обрабатывает строку BINARY как 0 в числовом контексте.

person Nick    schedule 02.02.2019
comment
интересно. почему я не могу хранить числа и извлекать их как таковые из двоичных типов? это работает в mysql 8 - person jeremy; 03.02.2019
comment
@jeremy посмотри мое обновление. Это показывает, что MySQL 5.7 обрабатывает строку как 0 в числовом контексте, где MySQL 8 приводит ее к числу. - person Nick; 03.02.2019
comment
хм... похоже, что bigint недостаточно велик для хранения 'b1d0c44a4eb5b5a9', так что я думаю, что это был мой просчет в том, что я хотел двоичный файл (8) в первую очередь. - person jeremy; 03.02.2019
comment
поскольку максимально возможное значение равно 0xFFFFFFFFFFFFFFFF, я думаю, что мне следует использовать десятичное число (64,0) - person jeremy; 03.02.2019
comment
@jeremy, тебе нужно использовать bigint unsigned, как я делал в своей демонстрации - person Nick; 03.02.2019
comment
Да, но по какой-то причине он говорит Numeric value out of range: 1264 Out of range value, когда я вставляю с UPDATE. см.: db-fiddle.com/f/mpqsUpZ1sv2kmvRwJrK5xL/4 - person jeremy; 03.02.2019
comment
Вам нужно сохранить значение в шестнадцатеричном формате, т.е. UPDATE `hashes` SET `hash` = 0x1f69f25228ed4a31 WHERE id = 1; db-fiddle.com/f/mpqsUpZ1sv2kmvRwJrK5xL /5 - person Nick; 03.02.2019
comment
Вы можете использовать CONV, например. UPDATE `hashes` SET `hash` = CONV('1f69f25228ed4a31', 16, 10) WHERE id = 1; db-fiddle.com/f/mpqsUpZ1sv2kmvRwJrK5xL/7 - person Nick; 03.02.2019
comment
Не волнуйтесь. Все части сервиса :-) - person Nick; 03.02.2019

arrow_upward
2
arrow_downward

Это не число, поэтому его нельзя использовать для математических расчетов:

`hash` binary(8) NOT NULL

Вместо этого используйте bigint:

`hash` bigint unsigned NOT NULL
person Wiimm    schedule 02.02.2019
comment
интересно. почему я не могу хранить числа и извлекать их как таковые из двоичных типов? это работает в mysql 8 - person jeremy; 03.02.2019

arrow_upward
2
arrow_downward

Попробуй это:

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;
person Jonas Tuemand Møller    schedule 03.02.2019