为什么这个简单的MySQL查询不返回行? [英] Why does this simple MySQL query not return the row?

问题描述

我在表users中有一行用户名test.但是由于某种原因，该查询返回了空的结果集.

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;

但是，如果删除`id` != null段，查询将返回结果id = 1.

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

但是1！= NULL.这是怎么回事?

But 1 != NULL. How is this happening?

id字段不可为空，并且会自动递增.

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

谢谢！

推荐答案

查询不会返回行，因为谓词"id != NULL"将永远不会返回TRUE.

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

这样做的原因是SQL中的布尔逻辑为三值.布尔值可以为TRUE，FALSE或NULL.

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

并且每当被比较的一个(或两个)值均为NULL时，不等式比较将返回NULL.

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

SQL标准要与NULL比较的方法是使用id IS NULL或id IS NOT NULL. MySQL还添加了一个方便的空安全比较运算符，该运算符将返回TRUE或FALSE:

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.或者，根据您的情况NOT (col <=> NULL)

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

这篇关于为什么这个简单的MySQL查询不返回行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！