mysql 选择等于空值,MySQL选择包含空值的字段

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.

We have got the following simple table:

id data1 data2 data3

(int) (varchar) (int) (int)

-------------------------------------

1 (empty) 123 456

2 (null) 321 654

3 abc 555 666

(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';

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.

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.

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.

Now the questions are:

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?

Generally: Should !='abc' match the recordsets 1 and 2 or should it only match id 1?

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!

Thanks in advance...

解决方案

Because null is a special case, if you want null values to be included, you should explicitly specify that you want them.

SELECT * FROM table WHERE (data1 <> 'abc' or data1 is null)