Can't see MySQL BIT field value when using SELECT

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

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

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

Method 2

Another way you can do it is

SELECT enabled+0 from my_table

Method 3

the simplest way is ORD function:

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

Method 4

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

Method 5

You could also try SELECT enabled&1 from my_table.

Method 6

Use HEX()

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

Method 7

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)

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating