MySQL选择包含空值的字段 [英] MySql select on fields containing null values
问题描述
在我们公司中，我们将Web应用程序(LAMP)从一台服务器(Ubuntu 10.04)移到了新服务器(Ubuntu 12.04.2).现在，我们遇到了一个以前从未见过的奇怪行为，而且我真的不知道从哪里开始.也许有人可以给我提示.
In our company we moved our web application (LAMP) from one server (Ubuntu 10.04) to a new server (Ubuntu 12.04.2). Now we encountered a strange behavior I haven't seen before and I really do not know where to begin. Maybe someone can give me hint.
我们有以下简单表格:
id data1 data2 data3
(int) (varchar) (int) (int)
-------------------------------------
1 (empty) 123 456
2 (null) 321 654
3 abc 555 666
(空)表示该字段包含一个空字符串. (null)表示该字段为null.现在，我们使用以下非常简单的查询:
(empty) means the field contains a empty string. (null) means that the field is null. Now we use the following very very simple query:
SELECT * FROM `table` WHERE `data1` != 'abc';
在我们的旧服务器上，查询返回了ID为1和2的行，我猜这是绝对正确的，因为!='abc'与这两个记录集匹配.
On our old server the query returned the lines with the ids 1 and 2 which, I guess, is absolutely correct since !='abc' matches those two recordsets.
在我们的新服务器上，查询仅返回ID为1的记录集.查询突然以某种方式忽略了选择字段中包含null的记录集.
On our new server the query only returns the recordset with the id 1. Recordsets containing null in the select fields are suddenly ignored by the query somehow.
只是为了更清楚一点:我知道可以使用IS NULL，但这将导致检查应用程序中符合此情况的所有查询和表.
Just to make it more clear: I know that IS NULL could be used, but that would result in checking all queries and tables in the application matching this situation.
现在的问题是:
我们是不是很幸运在旧服务器上通过返回第1行和第2行来达到查询的预期效果，还是在仅返回第1行的情况下新服务器就可以了?
Did we had luck on our old server that the query behaved as expected by returning lines 1 and 2 or does the new server behave correct by returning only line 1?
通常:!='abc'应该匹配记录集1和2，还是只匹配ID 1?
Generally: Should !='abc' match the recordsets 1 and 2 or should it only match id 1?
在mysql配置中是否有可以控制该行为的设置?我对此有些坚持.感谢您的帮助！
Is it possible that there is a setting in the mysql configuration that controlls that behaviour? I am a little stuck with that. Every help is appreciated!
预先感谢...
推荐答案
由于null是一种特殊情况，因此，如果要包含null值，则应明确指定要使用它们.
Because null is a special case, if you want null values to be included, you should explicitly specify that you want them.
选择*在table中(data1<>'abc'或data1为空)
SELECT * FROM table WHERE (data1 <> 'abc' or data1 is null)
这篇关于MySQL选择包含空值的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
