为什么在MySQL中为null<> null = null [英] Why is null<>null=null in mysql

问题描述

我正在学习MySql的教程，并且遇到了以下查询.

I am going through a tutorial for MySql, and I came through the following query.

mysql> select null <> null;
+--------------+
| null <> null |
+--------------+
|         NULL |
+--------------+

我不明白为什么结果为Null，我认为它应该是1还是0(基于其他比较运算符的结果)?

I did not understand why the result is Null, it needs to be either 1 or 0 I think (based on results from other comparison operators)?

为什么给出结果为Null.

Why it is giving the result as Null.

谢谢

推荐答案

因为出现在sql过滤器中的NULL上的任何比较运算符都应该(并且确实)使该行不被选中.

Because any comparison operator over NULL appearing in in a sql filter should (and does) make the row not be selected.

您应该使用空安全运算符<=>与包含NULL值和其他NOT NULL值的列进行比较，但是当两个操作数均为NULL时<=>将返回1，因为从不考虑NULL等于NULL.

You should use null safe operator <=> to compare to column containing NULL values and other NOT NULL value but <=> will return 1 when both operands are NULL because NULL is never considered equal to NULL.

这是使用null安全运算符的情况的示例:

您有一张桌子:

Phones
----
Number
CountryCode (can be NULL) 

您要选择所有非西班牙的电话号码(国家/地区代码34).第一次尝试通常是:

And you want to select all phone numbers not being from Spain (country code 34). The first try is usually:

SELECT Number FROM Phones WHERE CountryCode <> 34;

但是您注意到没有列出没有国家代码(NULL值)的电话，并且您希望将它们包括在结果中，因为它们也不来自西班牙:

But you notice that there are phones with no country code (NULL value) not being listed and you want to include them in your result because they are nor from Spain:

SELECT Number FROM Phones WHERE CountryCode <=> 34;

这篇关于为什么在MySQL中为null<> null = null的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！