Mysql: How to query a column whose type is bit?
Asked Answered
F

6

32

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!

Footpace answered 8/5, 2009 at 12:34 Comment(0)
Y
41
SELECT * FROM table WHERE active = (1)
Yurik answered 8/5, 2009 at 12:36 Comment(0)
W
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.

Wagram answered 8/5, 2009 at 12:40 Comment(3)
The first and the third entry are coorect whereas the second doesn't work, at least under my Mysql installation. Thanks anyway.Footpace
@Wagram 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.Papeterie
@Papeterie I don't see how BIT saves storage space. TINYINT(1) UNSIGNED 1 byte and BIT(1) 1 byte. See dev.mysql.com/doc/refman/8.0/en/storage-requirements.html (same with 5.7)Spirituel
N
7

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

Niphablepsia answered 8/5, 2009 at 12:38 Comment(1)
@Alvaro: This question is almost 4 years old!Footpace
P
7

Actually MySQL has built-in bit literals:

select*from table where active = 0b1
Papeterie answered 11/7, 2012 at 17:1 Comment(0)
C
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.

Cristycriswell answered 8/5, 2009 at 12:44 Comment(0)
T
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))

Tinsel answered 7/9, 2013 at 11:27 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.