How can I display a mysql bit(64) column as a string of ones and zeros?

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; 
  (0b0000000011111111000000001111111100000000111111110000000011111111)

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.

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

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

StackExchange Database Administrators Q#110978, answer score: 5