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

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';

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