MySQL:"= true"与“是真的"在BOOLEAN上.何时建议使用哪个?哪个独立于供应商? [英] MySQL: "= true" vs "is true" on BOOLEAN. When is it advisable to use which one? And Which one is vendor independent?

问题描述

MySQL提供了两种方法来检查boolean列的真值，即column_variable = true和column_variable is true.我创建了一个表格，插入了几个值&尝试了一些select语句.结果如下:

MySQL provides 2 ways to check truth value of boolean columns, those are column_variable = true and column_variable is true. I created a table, inserted few values & tried a few select statements. Here are the results:

首先，我创建了此表:

mysql> create table bool_test (
    -> id int unsigned not null auto_increment primary key,
    -> flag boolean );
Query OK, 0 rows affected (0.13 sec)

然后我插入了4行:

mysql> insert into bool_test(flag) values (true),(false),(9),(null);

mysql> select * from bool_test;
+----+------+
| id | flag |
+----+------+
|  1 |    1 |
|  2 |    0 |
|  3 |    9 |
|  4 | NULL |

这是我在此表上触发的所有select查询:

Here are all the select queries I fired on this table:

mysql> select * from bool_test where flag;
+----+------+
| id | flag |
+----+------+
|  1 |    1 |
|  3 |    9 |
+----+------+
2 rows in set (0.49 sec)

mysql> select * from bool_test where flag = true;
+----+------+
| id | flag |
+----+------+
|  1 |    1 |
+----+------+
1 row in set (0.02 sec)

mysql> select * from bool_test where flag is true;
+----+------+
| id | flag |
+----+------+
|  1 |    1 |
|  3 |    9 |
+----+------+
2 rows in set (0.04 sec)

mysql> select * from bool_test where flag = false;
+----+------+
| id | flag |
+----+------+
|  2 |    0 |
+----+------+
1 row in set (0.01 sec)

mysql> select * from bool_test where flag is false;
+----+------+
| id | flag |
+----+------+
|  2 |    0 |
+----+------+
1 row in set (0.00 sec)

mysql> select * from bool_test where !flag;
+----+------+
| id | flag |
+----+------+
|  2 |    0 |
+----+------+
1 row in set (0.00 sec)

mysql> select * from bool_test where not flag;
+----+------+
| id | flag |
+----+------+
|  2 |    0 |
+----+------+
1 row in set (0.00 sec)

mysql> select * from bool_test where flag != true;
+----+------+
| id | flag |
+----+------+
|  2 |    0 |
|  3 |    9 |
+----+------+
2 rows in set (0.00 sec)

mysql> select * from bool_test where flag is not true;
+----+------+
| id | flag |
+----+------+
|  2 |    0 |
|  4 | NULL |
+----+------+
2 rows in set (0.00 sec)

mysql> select * from bool_test where flag != false;
+----+------+
| id | flag |
+----+------+
|  1 |    1 |
|  3 |    9 |
+----+------+
2 rows in set (0.04 sec)

mysql> select * from bool_test where flag is not false;
+----+------+
| id | flag |
+----+------+
|  1 |    1 |
|  3 |    9 |
|  4 | NULL |
+----+------+
3 rows in set (0.00 sec)

我的问题是:什么时候建议使用is/is not，什么时候建议将=/!=与true/false一起使用?哪一个是与供应商无关的?

My Question is: when is it advisable to use is/is not and when is it advisable to use =/!= with true/false ? Which one is vendor independent?

推荐答案

MySQL实际上是在欺骗您.它根本没有布尔列类型:

MySQL is actually fooling you. It doesn't have a boolean column type at all:

BOOL，BOOLEAN

这些类型是TINYINT(1)的同义词.零值被认为 错误的.非零值被认为是真实的:

These types are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered true:

此外，布尔文字也不是:

常量TRUE和FALSE的取值分别为1和0.

The constants TRUE and FALSE evaluate to 1 and 0, respectively.

考虑到这一点

• 许多数据库系统也没有布尔值(至少不是在标准SQL和列类型中)
• MySQL没有简单的方法在BOOLEAN中强制执行0或1

• Many database systems do not have booleans either (not at least in standard SQL and column types)
• MySQL doesn't have an easy way to enforce 0 or 1 in BOOLEAN

我的结论是:

• 您必须使用WHERE IS flag或仅使用WHERE flag，因为=根本无法正常工作.哪个可能是偏好问题.
• 无论您选择什么，任何选项都不会与供应商无关.例如，Oracle甚至都不会运行它们.

• You'll have to use WHERE IS flag or just WHERE flag because = simply doesn't work correctly. Which one, is possibly a matter of preference.
• Whatever you choose, no option will be vendor independent. For instance, Oracle won't even run either of them.

:如果必须跨平台，我会这样做:

if cross-platform is a must, I'd go for this:

WHERE flag=0
WHERE flag<>0

我敢肯定我们都做了很多次.

I'm sure we've all done it lots of times.

这篇关于MySQL:"= true"与“是真的"在BOOLEAN上.何时建议使用哪个?哪个独立于供应商?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！