0

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

4
  • 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?)
    – Rick James
    Aug 28, 2016 at 22:40
  • What version of MySQL are you using? What client?
    – Rick James
    Aug 28, 2016 at 23:09
  • @RickJames PHPmyadminand Server version: 5.6.21 - MySQL Community Server (GPL)
    – Martin AJ
    Aug 28, 2016 at 23:52

1 Answer 1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.