Why does this simple MySQL query not return the ro

问题:

I have a row in the table users with the username test. For some reason, though, this query returns an empty result set.

SELECT `id` FROM `users` WHERE `username` = "test" AND `id` != null;

However, if I remove the `id` != null segment, the query returns the result id = 1.

But 1 != NULL. How is this happening?

The id field is non-nullable and is auto-increment.

Thanks!

回答1:

The query doesn't return a row because the predicate " id != NULL " will never return TRUE.

Th reason for this is that boolean logic in SQL is three valued. A boolean can have values of TRUE, FALSE or NULL.

And an inequality comparison will return NULL whenever one (or both) of the values being compared is NULL.

The SQL standard means to compare to a NULL is to use id IS NULL or id IS NOT NULL. MySQL also adds a convenient null-safe comparison operator which will return TRUE or FALSE:

col <=> NULL. Or, in your case NOT (col <=> NULL)

回答2:

Your method of checking for NULL is probably the issue. In MySQL, try the following:

SELECT `id` FROM `users` WHERE `username` = "test" AND `id` IS NOT NULL;

To check for NULL and an empty string, you can use:

SELECT `id` 
FROM `users` 
WHERE `username` = "test" 
AND (`id` IS NOT NULL OR `id` != "");

回答3:

try using IS NOT NULL

SELECT `id` FROM `users` WHERE `username` = "test" AND `id` IS NOT NULL

Have a look at the difference

SQL Fiddle DEMO

回答4:

By database definition in general ,Null is nothing and cannot be equated or compared with any other value. Hence ID=NUll or ID!=null wouldn't work.

回答5:

Try:

SELECT `id` FROM `users` WHERE `username` = "test" AND `id` != '';

OR

SELECT `id` FROM `users` WHERE `username` = "test" AND `id` iS NOT NULL;