CODEWITHSUNDEEP
mysqlstringbit-manipulationxorhamming-distance
Bat Masterson
Bat Masterson

Reputation: 1184

Performing bitwise operations on large bit strings in MySQL?

I've got a MySQL database with a large amount of 2048-bit binary strings (e.g '0111001...0101'). One calculation I'll need is the Hamming Distance (the total count of 1's in the XOR'd result) of these strings compared to some externally generated bitstring. In order to get an idea of how to write this query, I tried writing it for smaller bitstrings. Here's an example:

select BIT_COUNT(bin((b'0011100000') ^ (b'1111111111')))

The inner portion that computes the XOR works correctly, but BIT_COUNT returns strange results. This example returns 14, which is longer than the string itself.

So I have a few questions:

First, why is BIT_COUNT returning such strange results. Is it operating on a string rather than the binary string I'd like it to operate on? If so, how do I deal with this?

Second, notice that I'm casting (is that the right word here?) the strings as binary by prepending with a b. How would I do this with column names and variables? Clearly I can't simply prepend a b to a variable name, and I can't insert a space between. Any ideas?

Thanks,

EDIT: So here's a solution to the first problem:

select BIT_COUNT(b'0011100000' ^ b'1111111111')

There seems to be a problem when using this for larger strings (2048 bits). I tried:

select BIT_COUNT(b'001110...00011')

and it gives me results like 28, when the actual bitcount should be around 1024. If I remove the b, then it appears to max-out at 64. Any ideas on how to resolve this problem?

Upvotes: 0

Views: 1933

Answers (1)

zerkms
zerkms

Reputation: 255105

Just remove bin function. With it BIN_COUNT treats its argument as a chars string, not as a set of bits. So

select BIT_COUNT(b'0011100000' ^ b'1111111111')

will do the work

Upvotes: 1

Related Questions