MySQL：布尔值上的“= true”与“is true”。什么时候使用哪一种比较合适？哪一个是独立于供应商的？

MySQL: "= true" vs "is true" on BOOLEAN. When is it advisable to use which one? And Which one is vendor independent?

提问by Abhishek Oza

MySQL provides 2 ways to check truth value of booleancolumns, those are column_variable = trueand column_variable is true. I created a table, inserted few values & tried a few selectstatements. Here are the results:

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

First I created this table:

首先我创建了这个表：

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

Then I inserted 4 rows:

然后我插入了 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 |

Here are all the selectqueries I fired on this table:

以下是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)

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

我的问题是：什么时候使用is/is not是可取的，什么时候使用=/!=和true/是可取的false？哪一个是独立于供应商的？

采纳答案by álvaro González

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

MySQL实际上在愚弄你。它根本没有布尔列类型：

BOOL, BOOLEAN

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

BOOL, BOOLEAN

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

Also, the boolean literals are not such:

此外，布尔文字不是这样的：

The constants TRUEand FALSEevaluate to 1 and 0, respectively.

常数TRUE和 分别FALSE计算为 1 和 0。

Considering that:

考虑到：

• 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 0or 1in BOOLEAN

• 许多数据库系统也没有布尔值（至少在标准 SQL 和列类型中）
• MySQL 没有一个简单的方法来执行0或1输入BOOLEAN

My conclusion would be:

我的结论是：

• You'll have to use WHERE IS flagor just WHERE flagbecause =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.

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

---

---

Edit: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.

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

回答by LeeThree

If the flagcolumn is indexed and all values are either 0 or 1, where flag = trueis much faster than where flag is true.

如果flag列被索引并且所有值都是 0 或 1，where flag = true则比 快得多where flag is true。

During our testing, is trueresulted in a “full table scan” and took 1.121 seconds, while = truewas executed with “key lookup” and only took 0.167 seconds. The table had about 3 million rows.

在我们的测试中，is true产生了“全表扫描”，耗时 1.121 秒，而= true使用“键查找”执行，仅耗时 0.167 秒。该表有大约 300 万行。