# Why the value changes when I assign it to a variable?

## All we need is an easy explanation of the problem, so here it is.

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)?

## How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

### Method 1

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.)

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂