Mysql：如何查询类型为bit的列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/839596/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mysql: How to query a column whose type is bit?
提问by Luixv
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
生成的数据库表具有 BIT 数据类型。到现在为止还挺好。我想查询这个值，但我不知道该怎么做。我试过了
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!
提前致谢！
回答by Peter D
SELECT * FROM table WHERE active = (1)
回答by Andomar
According to this page, BIT is a synonym for TINYINT(1) for versions before 5.0.3.
根据此页面，对于5.0.3 之前的版本，BIT 是 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 entrysuggests to avoid the BIT data type altogether.
此博客条目建议完全避免 BIT 数据类型。
回答by RoguePlanetoid
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，所以如果这不起作用，请原谅我，因为我无法测试它。
回答by Pacerier
Actually MySQL has built-in bit literals:
实际上 MySQL 有内置的位文字：
select*from table where active = 0b1
回答by dfa
To specify bit values, b'value' notation can be used.
要指定位值，可以使用 b'value' 表示法。
回答by Jimi WIlls
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)...
更新将 isfeatured 中的 0 更改为 1，即类型 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
mysql Ver 14.14 Distrib 5.5.31，用于 debian-linux-gnu (x86_64) 使用 readline 6.2
and
和
/usr/sbin/mysqld Ver 5.5.31-0+wheezy1 for debian-linux-gnu on x86_64 ((Debian))
/usr/sbin/mysqld Ver 5.5.31-0+wheezy1 for debian-linux-gnu on x86_64 ((Debian))
