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

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

• The bitwise operations will still work fine against the integer. Aug 28, 2016 at 22:20
• (This seems to be a continuation of dba.stackexchange.com/questions/148111/… . Can someone explain things better than I did?) Aug 28, 2016 at 22:40
• What version of MySQL are you using? What client? Aug 28, 2016 at 23:09
• @RickJames `PHPmyadmin`and `Server version: 5.6.21 - MySQL Community Server (GPL)` Aug 28, 2016 at 23:52

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