mysql两汉字之间的汉明距离 [英] mysql hamming distance between two phash

问题描述

我有一个表A，该表的列为'template_phash'.我存储了从400K图像生成的phash.

I have a table A which has a column 'template_phash'. I store the phash generated from 400K images.

现在，我拍摄一张随机图像，并从该图像中生成一个phash.

Now I take a random image and generate a phash from that image.

现在如何查询，以便可以从表A中获取汉明距离差小于阈值(例如20)的记录.

Now how do I query so that I can get the record from table A which hamming distance difference is less than a threshold value, say 20.

我已经看到 SQL中二进制字符串的汉明距离，但无法弄清楚.

I have seen Hamming distance on binary strings in SQL, but couldn't figure it out.

我想我发现我需要创建一个函数来实现这一目标，但是如何实现呢?

I think I figured out that I need to make a function to achieve this but how?

我的两个手指都位于BigInt中，例如:7641692061273169067

请帮助我制作该函数，以便可以像

Please help me make the function so that I could query like

SELECT product_id, HAMMING_DISTANCE(phash1,  phash2) as hd 
FROM A 
WHERE hd < 20 ORDER BY hd ASC;

推荐答案

我发现汉明距离只是两个散列之间不同位数的计数.首先对两个哈希值进行异或运算，然后获得二进制哈希值的计数:

I figured out that the hamming distance is just the count of different bits between the two hashes. First xor the two hashes then get the count of binary ones:

SELECT product_id, BIT_COUNT(phash1 ^ phash2) as hd from A ORDER BY hd ASC;

这篇关于mysql两汉字之间的汉明距离的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！