We have a MySQL 5.5 database with a table that holds a number of 64-bit values which are used for calculating the hamming distance. The columns for these are defined as BIT(64) and are populated using the bit-field literal syntax like this (note the 0b prefix):

create table hash_values ( hash bit(64) );
insert into hash_values; 

For debugging purposes we need to display the bit values as strings of ones and zeros, but have only been able to do this with what feels like an ugly hack:

select conv(cast(hash as unsigned), 10, 2) hash from hash_values;
| hash                                                             |
| 0000000011111111000000001111111100000000111111110000000011111111 |
1 row in set (0.01 sec)

This first uses CAST() to get an integer representation; then uses the CONV() method to covert it from base-10 back to base-2. It seems to work, but I feel like we've overlooked a simple way to cast the bit value to a base-2 string.

2 Answers 2


You can also use BIN() function. You still have to LPAD() though, the higher order 0s are not displayed:

LPAD(BIN(hash), 64, '0')

After a little more messing around I've found that I can skip the CAST() and just use CONV(), but it still seems as though there should be a better way:

select conv(hash, 2, 2) hash from `table`;

I also am using LPAD() to get the full 64-characters, which would be nice to avoid if possible (I'm hoping there's a function that infer the length from the BIT(64) definition).

select lpad(conv(hash, 2, 2), 64, 0) hash from `table`;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.