MySQL: bool 值上的 "= true"与 "is true"。什么时候最好使用哪一个？哪一个是独立于供应商的？

Question

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

首先我创建了这张表:

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 查询:

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 ？哪一个是独立于供应商的？

Answer 1

MySQL 实际上是在愚弄你。它doesn't have a boolean column type at all :

BOOL, BOOLEAN

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

另外，boolean literals are not such :

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

考虑到:

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

我的结论是:

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

---

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

WHERE flag=0
WHERE flag<>0

我相信我们都做过很多次了。