How to get MySQL command line tool to show booleans stored as BIT sensibly by default

Question

I got a problem with selecting boolean types stored as BIT with MySQL. I know that I can get bit values shown in a sensible with with custom queries like with SELECT CAST(1=1 AS SIGNED INTEGER) or with SELECT BOOLFIELD + 0 ...

However, is there any way to get our booleans shown in a sensible way with command line client with queries like SELECT * FROM TABLE ?

UPDATE : At the moment I see only space in the results Example:

mysql> SELECT distinct foo, foo + 0 from table
+------+-------+
| foo  | foo_0 |
+------+-------+
|      |     0 |  <-- Only space
|     |     1 |   <-- Space, one space less
+------+-------+

With some googling, I found some (maybe related) bugs from MySQL bug DB (http://bugs.mysql.com/bug.php?id=28422, http://bugs.mysql.com/bug.php?id=43670) but not answer or fix?

Good points, sorry my questions was pretty unclear. At the moment we've ended up with BIT type to represent booleans (with JPA). — Touko, Sep 26 '13 at 04:22

score 6 · Answer 1 · edited May 23 '17 at 12:14

To store booleans, one really ought to use MySQL's BOOLEAN type (which is an alias for TINYINT(1), given that MySQL doesn't have real boolean types): 0 represents false and non-zero represents true.

Whilst it might feel like storing a boolean in a byte is more wasteful than in a BIT(1) column, one must remember that a few saved bits will translate into more bit operations for the CPU on data storage & retrieval; and I'm unsure whether most storage engines pad BIT columns to the next byte boundary anyway.

If you insist on using BIT type columns, you should be aware that they are returned as binary strings. The MySQL command line client (stupidly) attempts to render binary strings as textual (by applying its default character set), which is what causes the behaviour that you observe—there's no way to avoid this (other than to manipulate the field in the select list in order that it as returned as something other than a binary string, as you are already doing).

However, if you also insist on using SELECT * (which is bad practice, albeit somewhat more understandable from the command line client), you might consider defining a view in which the manipulation is performed and then SELECT from that. For example:

CREATE VIEW my_view AS SELECT foo + 0 AS foo, bar FROM my_table;

Then one could do:

SELECT * FROM my_view WHERE foo = 1 AND bar = 'wibble';

Good points, got to think through. And yes, SELECT * is only for ad-hoc querying & debugging with command line client to get easily an overview of table contents etc. — Touko, Sep 26 '13 at 08:31
@Touko: As for using `BOOLEAN` with JPA, see http://stackoverflow.com/a/10224905. — eggyal, Sep 26 '13 at 09:27

score 2 · Answer 2 · answered Mar 09 '21 at 11:04

A BIT ugly, but maybe some workaround: CASE WHEN ... THEN ... END

Instead of

> select
    guid,
    consumed,
    confirmed
  from Account
  where customerId = 'xxxx48' and name between xxxx and xxxx;
+--------------------------------------+----------+-----------+
| guid                                 | consumed | confirmed |
+--------------------------------------+----------+-----------+
| xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx |         |          |
| xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx |         |           |
| xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx |         |           |
| xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx |         |          |
| xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx |         |           |
+--------------------------------------+----------+-----------+

One could do:

> select
    guid,
    case when consumed then '1' when not consumed then '0' end as been_consumed,
    case when confirmed then '1' when not confirmed then '0' end as been_confirmed
  from Account
  where customerId = 'xxxx48' and name between xxxx and xxxx;
+--------------------------------------+---------------+----------------+
| guid                                 | been_consumed | been_confirmed |
+--------------------------------------+---------------+----------------+
| xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx | 1             | 1              |
| xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx | 1             | 0              |
| xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx | 1             | 0              |
| xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx | 1             | 1              |
| xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx | 1             | 0              |
+--------------------------------------+---------------+----------------+

How to get MySQL command line tool to show booleans stored as BIT sensibly by default

2 Answers2