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

问题描述

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

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.

我的查询看起来像这样:

My query looks something like this:

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始终为false).

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

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