How can I assign a bit datatype value to a variable?

Question

I have a table like this:

// mytable
+---------+-------------+
|   id    |    bitmap   |
+---------+-------------+
| int(11) |   BIT(10)   |
+---------+-------------+
| 1       | 01111111000 |
| 2       | 01111111111 |
| 3       | 00000000001 |
+---------+-------------+

And these are some examples of both current and expected output:

Example1:

SELECT bitmap INTO @var FROM mytable WHERE id = 1;
SELECT @var;
/* Current output: 1016
   Expected output: 01111111000
*/

Example2:

SELECT bitmap INTO @var FROM mytable WHERE id = 2;
SELECT @var;
/* Current output: 1023
   Expected output: 01111111111
*/

Example3:

SELECT bitmap INTO @var FROM mytable WHERE id = 3;
SELECT @var;
/* Current output: 1
   Expected output: 00000000001
*/

Well how can I do that? As you see, I'm trying to assign the original bit value of that column to that variable (but seems there is a conversion, how can I avoid that?). I really don't know why @var isn't contain a binary value.

Answer 1

You can feed the binary representation of that bitmap column value into @var using CONV(N,from_base,to_base) function.

SELECT CONV(bitmap,2,2) INTO @var FROM mytable WHERE id = 1;
SELECT @var;

Note:

CONV() function

MySQL CONV() converts a number from one numeric base number system to another numeric base number system. After the conversion the function returns a string representation of the number.

When the argument defined is a NULL, the return value will be NULL.

The minimum base is 2 and maximum base is 36. If the base to be converted to is a negative number, the number is regarded as a signed number. Otherwise, it is treated as unsigned.

Syntax :

CONV(num , from_base , to_base );

Arguments

Name       Description
num        A number.
from_base  Existing base of the number num.
to_base    Base of the number num after conversion.