Mysql无法选中表中的记录

Question

我有一个 mysql 表，其中有一个名为“status”的可为空的 int 列，我在表中有两条记录，其中一条状态为 2，而另一条为 NULL，但是当我选择带有查询的记录时 ' status!=2'，记录(status=null)不显示。

mysql> 
mysql> 
mysql> desc admin_user;
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| acct_name   | varchar(32)      | YES  |     | NULL    |                |
| password    | varchar(32)      | YES  |     | NULL    |                |
| user_name   | varchar(32)      | YES  |     | NULL    |                |
| description | varchar(128)     | YES  |     | NULL    |                |
| status      | int(11)          | YES  |     | NULL    |                |
| role        | int(11)          | NO   |     | 1       |                |
| create_date | date             | YES  |     | NULL    |                |
| update_date | date             | YES  |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

mysql> select id, acct_name, status from admin_user;
+----+-----------+--------+
| id | acct_name | status |
+----+-----------+--------+
|  1 | letme     |   NULL |
|  3 | admin     |      2 |
+----+-----------+--------+
2 rows in set (0.00 sec)

mysql> select id, acct_name, status from admin_user where status=2;
+----+-----------+--------+
| id | acct_name | status |
+----+-----------+--------+
|  3 | admin     |      2 |
+----+-----------+--------+
1 row in set (0.00 sec)

mysql> select id, acct_name, status from admin_user where status IS NULL;
+----+-----------+--------+
| id | acct_name | status |
+----+-----------+--------+
|  1 | letme     |   NULL |
+----+-----------+--------+
1 row in set (0.00 sec)

mysql> select id, acct_name, status from admin_user where status!=2;
Empty set (0.00 sec)

mysql>

如您所见，无法使用查询“status!=2”选择状态为 NULL 的记录。我也试过“状态 <> 2”。有人可以帮忙吗？

Answer 1

空值实际上是 interesting .

The NULL value can be surprising until you get used to it. Conceptually, NULL means “a missing unknown value” and it is treated somewhat differently from other values.

还有什么

You cannot use arithmetic comparison operators such as =, <, or <> to test for NULL

有很多不同的写法。其中之一是:

select id, acct_name, status from admin_user where status IS NULL
  OR status != 2

如@shA.t 建议的那样。

select id, acct_name, status from table1 where COALESCE(status, 0)  != 2

只需检查 0 确实是一个没有出现在表中其他任何地方的数字。