MySQL:选择列为空的行 [英] MySQL: selecting rows where a column is null

问题描述

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

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.

我的查询如下所示:

SELECT pid FROM planets WHERE userid = NULL

每次都是空集.

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

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.

推荐答案

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

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

包括自身(即:NULL = NULL 总是假的).

参见规则 3 https://en.wikipedia.org/wiki/Codd%27s_12_rules

这篇关于MySQL:选择列为空的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！