Mysql – Why the value changes when I assign it to a variable

MySQL

I have a table like this:

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

As you see, the value of ban column is a binary number (its datatype is BIT(11)). and when I select it, the value is still a binary number, like this:

SELECT ban FROM mytable WHERE id = 1;
-- output: 01111111000

But when I assign it to a variable, surprisingly the value changes, like this:

SELECT ban INTO @ban FROM mytable WHERE id = 1;
SELECT @ban;
-- output: 1016

Well what happens when I set 01111111000 into @ban? Why it will be changed (1016)?

You know? In reality, each digit (bit) of that 11-bit number (01111111000) refers to something. For example, the second bit determines user's limitation on voting. I mean it the first digit (right to left) is 1, it means the user can vote, if it is 0 it means the user is banned for voting.

Anyway, I don't need to get a decimal like this 1016. I need to assign and keep exactly that 11-bit number to @ban variable. Because I need to check it like this:

IF ( IFNULL((@ban & b'10' > 0), 0) < 1 ) THEN
 -- user can vote
ELSE 
-- user cannot vote
ENDIF;

So there is different action for each digit. That's why I don't want a 10-based number like 1016. I want a bit-based number like this 01111111000 in that variable.

To make it more clear, here is the task of third digit: 

IF ( IFNULL((@ban & b'100' > 0), 0) < 1 ) THEN
 -- user can flag
ELSE 
-- user cannot flag
ENDIF;

See? the third digit (of that 11-bit number) determines user's flagging ability (limitation).

Ok, in conclusion, how can I assign a bit value to a variable without converting its datatype (which seems converting will be happened automatically, so how can I avoid that)?

Best Answer

Maybe this quote from http://dev.mysql.com/doc/refman/5.6/en/bit-functions.html will help clarify:

Bit functions and operators comprise BIT_COUNT(), BIT_AND(), BIT_OR(), BIT_XOR(), &, |, ^, ~, <<, and >>.

Bit functions and operators require BIGINT (64-bit integer) arguments and return BIGINT values, so they have a maximum range of 64 bits. Arguments of other types (such as the BINARY and VARBINARY binary string types) are converted to BIGINT and truncation might occur.

(The 5.7 man page hints that some future version will extend BIT beyond 64 bits.)

Related Question