Mysql无法选择表中的记录 [英] Mysql can't select the record in the table

问题描述

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

I have a mysql table with a null-able int column named 'status', and I have two records in the table where one of the status is 2 when the other is NULL, but when I select the records with query 'status!=2', the record (status=null) is not shown.

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'.有人可以帮忙吗?

As you can see, the record whose status is NULL can not be selected using the query 'status!=2'. I also tried 'status<>2'. Can anybody help?

推荐答案

Nulls 实际上是 有趣.

Nulls are really interesting.

在您习惯之前，NULL 值可能会令人惊讶.从概念上讲，NULL 表示丢失的未知值"，它被视为与其他值有些不同.

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.

还有什么

您不能使用算术比较运算符，例如 =、< 或 <>测试是否为 NULL

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

有很多不同的方法来写这个.其中之一是:

There are lots of different ways to write this. One of them being:

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

或者如@shA.t 建议的那样.

Alterantively as @shA.t suggested.

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

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

Just check that 0 is indeed a number that doesn't appear any where else in the table.

这篇关于Mysql无法选择表中的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！