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