mysql is true_MySQL：“ = true”与“是真的”在BOOLEAN上。何时建议使用哪个？哪个独立于供应商？...

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:

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:

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 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)

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?

解决方案BOOL, BOOLEAN

These types are synonyms for TINYINT(1). A value of zero is considered

false. Nonzero values are considered true:

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

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

My conclusion would be:

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.

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.