Reputation: 8710
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?
Upvotes: 32
Views: 45575
Reputation: 89793
Actually MySQL has built-in bit literals:
select*from table where active = 0b1
Upvotes: 7
Reputation: 348
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))
Upvotes: 0
Reputation: 238296
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.
Upvotes: 24
Reputation: 4576
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.
Upvotes: 6