Can't see MySQL BIT field value when using SELECT
MysqlSqlBitMysql Problem Overview
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
Mysql Solutions
Solution 1 - Mysql
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.txt
file 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)
Solution 2 - Mysql
Another way you can do it is
SELECT enabled+0 from my_table
Solution 3 - Mysql
the simplest way is ORD
function:
SELECT ORD(`enabled`) AS `enabled` FROM `my_table`
Solution 4 - Mysql
>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
Solution 5 - Mysql
You could also try SELECT enabled&1 from my_table
.
Solution 6 - Mysql
Use HEX()
Like:
SELECT id, HEX(enabled) AS enabled FROM my_table
Solution 7 - Mysql
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)