31

Hi I am using hibernate and Mysql. I have a class with a boolean attribute called 'active'.

The generated database table has the BIT data type. So far so good. I want to query this value but I don't know how to do it. I've tried 

 SELECT * from table where active = 1

doesn't work, neither the following

 SELECT * from table where active = true

I didn't find anything neither in the reference manual nor at Stackoveflow.

Any hint?

Thanks in advance!

Luixv
  • 8,590
  • 21
  • 84
  • 121

6 Answers6

41
SELECT * FROM table WHERE active = (1)
Jesper Fyhr Knudsen
  • 7,802
  • 2
  • 35
  • 46
Peter D
  • 4,851
  • 2
  • 30
  • 30
24

According to this page, BIT is a synonym for TINYINT(1) for versions before 5.0.3.

Have you tried these?

SELECT * from table where active = (1)
SELECT * from table where active = 'true'
SELECT * from table where active = b'1'

This blog entry suggests to avoid the BIT data type altogether.

Pacerier
  • 86,231
  • 106
  • 366
  • 634
Andomar
  • 232,371
  • 49
  • 380
  • 404
  • The first and the third entry are coorect whereas the second doesn't work, at least under my Mysql installation. Thanks anyway. – Luixv May 08 '09 at 20:07
  • @Andomar Disagree to avoid BIT data type. The disadvantage is interoperability with older versions, assuming you don't need it, BIT actually stores data more compactly and hence save storage space. – Pacerier Jul 11 '12 at 17:03
  • @Pacerier I don't see how BIT saves storage space. TINYINT(1) UNSIGNED 1 byte and BIT(1) 1 byte. See https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html (same with 5.7) – Șerban Ghiță Jul 15 '19 at 12:44
7

Actually MySQL has built-in bit literals:

select*from table where active = 0b1
Pacerier
  • 86,231
  • 106
  • 366
  • 634
7

To specify bit values, b'value' notation can be used.

dfa
  • 114,442
  • 31
  • 189
  • 228
6

Have you tried casting it to an Integer for comparison

SELECT * from table where cast(active as unsigned) = 1

I use MS SQL most of the time so forgive me if this does not work as I cannot test it.

RoguePlanetoid
  • 4,516
  • 7
  • 47
  • 64
0

Well, for both comparisons and updates, 0 and 1 work for me:

Here's a field of type bit(1), one row, the field is currently false:

mysql> select isfeatured from nodes where isfeatured = 1;
Empty set (0.00 sec)

mysql> select isfeatured from nodes where isfeatured = 0;
+------------+
| isfeatured |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

Update changing 0 to 1 in isfeatured, which is type bit(1)...

mysql> update nodes set isfeatured=1 where isfeatured = 0;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

One row changed... Try it again:

mysql> update nodes set isfeatured=1 where isfeatured = 0;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

No rows changed as expected.

Same select queries as before:

mysql> select isfeatured from nodes where isfeatured = 1;
+------------+
| isfeatured |
+------------+
|           |
+------------+
1 row in set (0.00 sec)

mysql> select isfeatured from nodes where isfeatured = 0;
Empty set (0.01 sec)

See, it works.

I'm using:

mysql Ver 14.14 Distrib 5.5.31, for debian-linux-gnu (x86_64) using readline 6.2

and

/usr/sbin/mysqld Ver 5.5.31-0+wheezy1 for debian-linux-gnu on x86_64 ((Debian))

Jimi WIlls
  • 348
  • 1
  • 10