Skip to content

How to Retrieve Bit-Typed Values Literally From MySQL

  • by
When you retrieve bit-typed column, you might get an unrecognized, garbled or blank text, for example:
mysql> select page_name, privileges from lists where page_id = 122;
+-----------+------------+
| page_name | privileges |
+-----------+------------+
| mod_name  |             |
+-----------+------------+
1 row in set (0.00 sec)

There's nothing wrong with your values, it's just because the bit value needs to be displayed in recognized forms (e.g. a decimal number).

If you want a decimal number, you can just add a zero to force the value to cast into a number.
mysql> select page_name, privileges+0 number from lists where page_id = 122;
+-----------+--------+
| page_name | number |
+-----------+--------+
| mod_name  |    255 |
+-----------+--------+
1 row in set (0.00 sec)

If you still want a bit-patterned output, you can use BIN function to convert it into a string.
mysql> select page_name, BIN(privileges+0) bits from lists where page_id = 122;
+-----------+----------+
| page_name | bits     |
+-----------+----------+
| mod_name  | 11111111 |
+-----------+----------+
1 row in set (0.00 sec)

In MySQL, you have to use a very special notation to represent a bit value as an input. For example, a bit value 11111111 should be noted as b'11111111'. For example:
mysql> select b'11111111'+0 number;
+--------+
| number |
+--------+
|    255 |
+--------+
1 row in set (0.00 sec)

mysql> select b'1111'+0 number;
+--------+
| number |
+--------+
|     15 |
+--------+
1 row in set (0.00 sec)

Please note that, there's no need to do any conversions explicitly if you just want a bitwise operation in MySQL. For example, an AND and an OR bitwise operations as below:
mysql> select 255 & b'00000001' bits;
+------+
| bits |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select 255 | b'00000001' bits;
+------+
| bits |
+------+
|  255 |
+------+
1 row in set (0.00 sec)

Actually, MySQL will do the conversion implicitly. That's it.

If you have problems with bitwise in PHP, you may refer to my another post:
A Bitwise Operation Pitfall When String Operands Involved in PHP
Tags:

Leave a Reply

Your email address will not be published.