CODEWITHSUNDEEP
mysqlselectbooleancommand-line-interfacebit
Touko
Touko

Reputation: 11779

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

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?

Upvotes: 10

Views: 5428

Answers (2)

t3az0r
t3az0r

Reputation: 459

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

Upvotes: 2

eggyal
eggyal

Reputation: 125865

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

Upvotes: 8

Related Questions