Can't see MySQL BIT field value when using SELECT

Question

my_table contains the enabled field which is defined as: enabled BIT NOT NULL DEFAULT 0.

This table has multiple rows with enabled = b'0', and multiple rows with enabled = b'1'.

However, both this:

SELECT * from my_table WHERE enabled = b'0'; 

and this:

SELECT * from my_table WHERE enabled = b'1'; 

show blank in the enabled column:

+----+---------+ | id | enabled | +----+---------+ |  1 |         | |  2 |         | +----+---------+ 

Why is that? How could I see the value of the enabled field?

---

$ mysql --version mysql  Ver 14.14 Distrib 5.1.63, for debian-linux-gnu (x86_64) using readline 6.1 

---

Answer 1

The reason why you can't see it in terminal is because bit values are non printable characters.

Lets insert following values:

INSERT INTO `my_table` (`ID`, `enabled`) VALUES (1,b'1'),(2,b'0'); 

Then select them to file:

mysql> SELECT * FROM my_table INTO OUTFILE '/tmp/my_table.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'; 

First lets view our /tmp/my_table.txtfile as plain text:

"1"," "
"2"," "

and then in hex view:

22 31 22 2C 22 01 22 0A 22 32 22 2C 22 00 22 0A

To be able to see those values you can simply CAST them in SELECT:

SELECT id, CAST(enabled AS UNSIGNED) AS enabled FROM my_table 

And that will produce the following output:

+----+---------+ | id | enabled | +----+---------+ |  1 |       1 | |  2 |       0 | +----+---------+ 2 rows in set (0.00 sec)