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

Why are you using `BIT` instead of `BOOLEAN`? Will you need to store more than one flag in that column? — cdhowie, Jan 09 '13 at 23:51
Thats odd. I can't reproduce that. Works [here](http://sqlfiddle.com/#!2/64482/1) — juergen d, Jan 10 '13 at 00:01

peterm · Accepted Answer · 2013-01-10T01:31:38.303

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)

how to enable/view this when using `select * from my_table`? — Shantaram Tupe, Dec 15 '17 at 14:25
For anyone comming after, you can't do this on a select * from thetable. You need to list all fields you want. — Kemmotar, Feb 25 '21 at 08:05

score 33 · Answer 2 · answered May 19 '16 at 10:48

33

Another way you can do it is

SELECT enabled+0 from my_table

answered May 19 '16 at 10:48

L-Samuels

2,712
9
34
42

علیرضا · Answer 3 · 2017-11-19T03:43:40.423

30

the simplest way is ORD function:

SELECT ORD(`enabled`) AS `enabled` FROM `my_table`

edited Nov 19 '17 at 03:43

answered Sep 06 '15 at 07:08

علیرضا

2,434
1
27
33

score 6 · Answer 4 · answered Sep 05 '16 at 03:36

6

Bit values are returned as binary values. To display them in printable form, add 0 or use a conversion function such as BIN().

https://dev.mysql.com/doc/refman/5.7/en/bit-field-literals.html

answered Sep 05 '16 at 03:36

tingshuai.yts

61
1
3

SELECT BIN(b'1'); returns 0 – Jacob Sánchez Jul 10 '20 at 22:01
Usage: `SELECT BIN(my_column) FROM my_table` or `SELECT my_column+0 FROM my_table` – sshow Aug 11 '22 at 10:02

score 2 · Answer 5 · edited Oct 21 '20 at 05:36

2

Use HEX()

Like: SELECT id, HEX(enabled) AS enabled FROM my_table

edited Oct 21 '20 at 05:36

ma_jafari

1,021
1
6
24

answered Oct 20 '20 at 13:32

Artem

21
1

score 0 · Answer 6 · answered Jan 01 '19 at 19:44

0

You could also try SELECT enabled&1 from my_table.

answered Jan 01 '19 at 19:44

Arjun Aravind

143
2
4

score 0 · Answer 7 · answered Jan 22 '21 at 14:31

to convert a bit field value to a human readable string, use the built-in EXPORT_SET function, the simple example to convert a column of type bit(1) to a "Y" or "N" value would be

EXPORT_SET(column, 'Y', 'N')

You could also convert a bit(8) value to binary representation of the byte

EXPORT_SET(column, '1', '0', '', 8)

Can't see MySQL BIT field value when using SELECT

7 Answers7

Linked