MySql select on fields containing null values

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...

回答1:

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 data is null)

The right server behavior, is ignore nulls unless you ask for them...

回答2:

SELECT * FROM `table` WHERE `data1` = '';