Mysql:如何查询类型为bit的列?

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

你好，我在使用hibernate和Mysql。我有一个名为“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.

我在参考手册和Stackoveflow上都找不到任何东西。

Any hint?

有提示吗?

Thanks in advance!

提前谢谢!

6 个解决方案

#1

SELECT * FROM table WHERE active = (1)

#2

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

根据这一页，BIT是在5.0.3之前的版本的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.

这篇博客文章建议完全避免位数据类型。

#3

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

要指定位值，可以使用b'value'表示法。

#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.

我大部分时间都在使用MS SQL，所以如果我不能测试它，请原谅我。

#5

Actually MySQL has built-in bit literals:

实际上MySQL有内置的位文字:

select*from table where active = 0b1

#6

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

对于比较和更新，0和1对我都适用:

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

这是类型为bit(1)的字段，一行，字段当前为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)...

在isfeature中更新0到1，这是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

对于使用readline 6.2的debian-linux-gnu (x86_64)， mysql Ver 14.14发行者为5.5.5.31

and

和

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

在x86_64 (Debian)上，Debian -linux-gnu (Debian -linux-gnu)

#1

SELECT * FROM table WHERE active = (1)

#2