MySQL：选择列为空的行

MySQL: selecting rows where a column is null

提问by The.Anti.9

I'm having a problem where when I try to select the rows that have a NULL for a certain column, it returns an empty set. However, when I look at the table in phpMyAdmin, it says null for most of the rows.

我遇到了一个问题，当我尝试为某个列选择具有 NULL 的行时，它返回一个空集。但是，当我查看 phpMyAdmin 中的表时，大多数行都显示为 null。

My query looks something like this:

我的查询看起来像这样：

SELECT pid FROM planets WHERE userid = NULL

Empty set every time.

每次都清空。

A lot of places said to make sure it's not stored as "NULL" or "null" instead of an actual value, and one said to try looking for just a space (userid = ' ') but none of these have worked. There was a suggestion to not use MyISAM and use innoDB because MyISAM has trouble storing null. I switched the table to innoDB but now I feel like the problem may be that it still isn't actually null because of the way it might convert it. I'd like to do this without having to recreate the table as innoDB or anything else, but if I have to, I can certainly try that.

很多地方都说要确保它不会存储为“NULL”或“null”而不是实际值，有人说尝试只查找一个空格 ( userid = ' ') 但这些都没有奏效。有人建议不要使用 MyISAM 而使用 innoDB，因为 MyISAM 无法存储 null。我将表切换到 innoDB，但现在我觉得问题可能是它实际上仍然不是 null，因为它可能转换它的方式。我想这样做而不必将表重新创建为 innoDB 或其他任何东西，但如果必须的话，我当然可以尝试。

回答by Marc B

SQL NULL's special, and you have to do WHERE field IS NULL, as NULL cannot be equal to anything,

SQL NULL 的特殊性，你必须这样做WHERE field IS NULL，因为 NULL 不能等于任何东西，

including itself (ie: NULL = NULL is always false).

包括它自己（即：NULL = NULL 总是假的）。

See Rule 3https://en.wikipedia.org/wiki/Codd%27s_12_rules

见Rule 3https://en.wikipedia.org/wiki/Codd%27s_12_rules

回答by Michael Pakhantsov

SELECT pid FROM planets WHERE userid IS NULL

回答by Aniket Kulkarni

As all are given answers I want to add little more. I had also faced the same issue.

由于所有人都给出了答案，我想再补充一点。我也遇到过同样的问题。

Why did your query fail? You have,

为什么您的查询失败？你有，

SELECT pid FROM planets WHERE userid = NULL;

This will not give you the expected result, because from mysql doc

这不会给你预期的结果，因为来自mysql doc

In SQL, the NULL value is never true in comparison to any other value, even NULL.An expression that contains NULL always produces a NULL value unless otherwise indicated in the documentation for the operators and functions involved in the expression.

在 SQL 中，与任何其他值（甚至 NULL）相比，NULL 值永远不会为真。包含 NULL 的表达式总是产生一个 NULL 值，除非在表达式中涉及的运算符和函数的文档中另有说明。

Emphasis mine.

强调我的。

To search for column values that are NULL, you cannot use an expr = NULLtest. The following statement returns no rows, because expr = NULLis never true for any expression

要搜索 的列值NULL，您不能使用expr = NULL测试。以下语句不返回任何行，因为expr = NULL对于任何表达式都不会为真

Solution

解决方案

SELECT pid FROM planets WHERE userid IS NULL; 

To test for NULL, use the IS NULLand IS NOT NULLoperators.

要测试NULL，请使用IS NULL和IS NOT NULL运算符。

• operator IS NULLtests whether a value is NULL.
• operator IS NOT NULLtests whether a value is not NULL.
• MySQL comparison operators

• 运算符 IS NULL测试值是否为NULL。
• 运算符 IS NOT NULL测试值是否不是NULL。
• MySQL 比较运算符

回答by obe

There's also a <=>operator:

还有一个<=>运算符：

SELECT pid FROM planets WHERE userid <=> NULL

Would work. The nice thing is that <=>can also be used with non-NULL values:

会工作。好的是它<=>也可以用于非 NULL 值：

SELECT NULL <=> NULLyields 1.

SELECT NULL <=> NULL产量1。

SELECT 42 <=> 42yields 1as well.

SELECT 42 <=> 42产量也1一样。

See here: https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#operator_equal-to

见这里：https: //dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#operator_equal-to

回答by minhas23

Info from http://w3schools.com/sql/sql_null_values.asp:

来自http://w3schools.com/sql/sql_null_values.asp 的信息：

1) NULL values represent missing unknown data.

2) By default, a table column can hold NULL values.

3) NULL values are treated differently from other values

4) It is not possible to compare NULL and 0; they are not equivalent.

5) It is not possible to test for NULL values with comparison operators, such as =, <, or <>.

6) We will have to use the IS NULL and IS NOT NULL operators instead

1) NULL 值表示缺失的未知数据。

2) 默认情况下，表列可以保存 NULL 值。

3) NULL 值与其他值的处理方式不同

4）无法比较NULL和0；它们不是等价的。

5) 无法使用比较运算符（例如 =、< 或 <>）测试 NULL 值。

6) 我们将不得不使用 IS NULL 和 IS NOT NULL 运算符代替

So in case of your problem:

因此，如果您遇到问题：

SELECT pid FROM planets WHERE userid IS NULL

回答by RichKenny

Had the same issue where query:

有同样的问题，其中查询：

SELECT * FROM 'column' WHERE 'column' IS NULL; 

returned no values. Seems to be an issue with MyISAM and the same query on the data in InnoDB returned expected results.

没有返回值。似乎是 MyISAM 的问题，对 InnoDB 中数据的相同查询返回了预期结果。

Went with:

去了：

SELECT * FROM 'column' WHERE 'column' = ' '; 

Returned all expected results.

返回所有预期结果。

回答by user

SELECT pid FROM planets WHERE userid is null;

回答by user2406670

I had the same issue when converting databases from Access to MySQL (using vb.net to communicate with the database).

将数据库从 Access 转换为 MySQL（使用 vb.net 与数据库通信）时，我遇到了同样的问题。

I needed to assess if a field (field type varchar(1)) was null.

我需要评估一个字段（字段类型 varchar(1)）是否为空。

This statement worked for my scenario:

此语句适用于我的场景：

SELECT * FROM [table name] WHERE [field name] = ''