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

Question

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!

Answer 1

SELECT * from table where active = (1)

Answer 2

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

Answer 3

According to this page, BIT used to be synonym for TINYINT(1).

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.

Answer 4

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.