mysql 查询 无 返回,为什么这个简单的MySQL查询不返回行？

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!

解决方案

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)