MySQL provides a nice operator <=> that works with comparisons that could contain a null such as null <=> null or null <=> 5 etc. giving back intuitive results as many programming languages. Whereas the normal equals operator always just returns null, which catches many new MySQL users such as myself awry.
MySQL提供了一个很好的运算符<=>，它可以使用包含null的比较，例如null <=> null或null <=> 5等，可以像许多编程语言一样给出直观的结果。正常的equals运算符总是只返回null，这会吸引许多新的MySQL用户，例如我自己。
Is there a reason MySQL has both and not JUST the functionality in <=> ? Who really needs an operator that is effectively undefined with built in language types?
MySQL有两个而不仅仅是<=>的功能吗？谁真的需要一个内置语言类型有效未定义的运算符？
5 个解决方案
#1
10
Who really needs an operator that is effectively undefined with built in language types?
谁真的需要一个内置语言类型有效未定义的运算符？
You asked for some real-world examples. Here's a spurious one. Let's say that you have a residential youth programme or similar, and one of the requirements is that the kids only share a room with someone of the same sex. You have a nullable M/F field in your database - nullable because your data feed is incomplete (you're still chasing down some of the data). Your room-matching code should definitely not match students where t1.Gender<=>t2.Gender, because it could end up matching two kids of unknown gender, who might be of opposite genders. Instead, you match where they're equal and not both null.
你问了一些现实世界的例子。这是一个虚假的。假设您有一个住宿青少年计划或类似计划，其中一个要求是，孩子们只与同性别的人共用一个房间。您的数据库中有一个可以为空的M / F字段 - 可以为空，因为您的数据提要不完整（您仍在追逐某些数据）。您的房间匹配代码绝对不能与t1.Gender <=> t2.Gender的学生匹配，因为它最终可能会匹配两个性别不明的孩子，他们可能是性别相反的。相反，你匹配它们相等的位置而不是空。
That's just one example. I admit that the behaviour of NULL and the = operator have caused a lot of confusion over the years, but ultimately the fault probably lies with the plethora of online MySQL tutorials that make no mention of how NULL interacts with operators, nor of the existence of the <=> operator.
这只是一个例子。我承认NULL和=运算符的行为多年来引起了很多混乱，但最终的错误可能在于过多的在线MySQL教程没有提到NULL如何与运算符交互，也没有提到存在<=>运算符。
#2
6
The big difference between null in mySQL and in programming languages is that in mySQL, null means unknown value while in programming it means undefined value.
mySQL和编程语言中null的最大区别在于，在mySQL中，null意味着未知值，而在编程中它意味着未定义的值。
In mySQL, null does not equal null (unknown does not equal unknown). While in programming languages, null does equal null (undefined equals undefined).
在mySQL中，null不等于null（unknown不等于unknown）。在编程语言中，null确实等于null（undefined等于undefined）。
#3
4
Who really needs an operator that is effectively undefined with built in language types?
谁真的需要一个内置语言类型有效未定义的运算符？
you also need it for relations inside your database. specially if you are using foreign key constraints.
你还需要它用于数据库内的关系。特别是如果您使用外键约束。
for example if you have a table for tasks (in your company). then you assign these tasks to employees. so you have a relation from your tasks-table to your employees-table.
and there will always be some unassigned tasks. in this case the field in your tasks-table you use for the relation to the employees table will contain NULL. this will make sure, that this task is unassigned. which means: there is no possibility that there is a relation to the employees table.
例如，如果您有一个任务表（在您的公司）。然后将这些任务分配给员工。所以你有从task-table到employees-table的关系。总会有一些未分配的任务。在这种情况下，您用于与employees表的关系的tasks-table中的字段将包含NULL。这将确保此任务未分配。这意味着：不可能与employees表有关系。
if NULL = NULL would be true, then in my example there would be always the possibility that the foreign key in the employees table also is NULL. thus the task would be assigned to one or some employees. and you never would be able to know for sure wheter a task is assigned to some employee or not.
如果NULL = NULL则为true，那么在我的示例中，employees表中的外键也总是为NULL。因此，任务将分配给一个或一些员工。并且您永远无法确定是否将任务分配给某个员工。
#4
2
Is there a reason MySql has both and not JUST the functionality in <=> ? The operators are completely different from each other.
有没有理由MySql同时具有<=>中的功能？运营商彼此完全不同。
<=> performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand is NULL.
<=>执行与=运算符相等的比较，但如果两个操作数均为NULL，则返回1而不是NULL;如果一个操作数为NULL，则返回0而不是NULL。
Who really needs an operator that is effectively undefined with built in language types?
谁真的需要一个内置语言类型有效未定义的运算符？
This depends on case, just because you haven't encountered such cases, does not mean nobody needs it.
这取决于案例，仅仅因为你没有遇到过这种情况，并不代表没有人需要它。
#5
1
Yes.
是。
This must be because relational databases use the theory of three-valued logic (TRUE, NULL, FALSE).
这必须是因为关系数据库使用三值逻辑理论（TRUE，NULL，FALSE）。
And the three-valued logic must work so because it must be internally consistent.
三值逻辑必须工作，因为它必须是内部一致的。
It follows from the rules of mathematics.
它遵循数学规则。
Comparisons with NULL and the three-valued logic
与NULL和三值逻辑的比较
#1
10
Who really needs an operator that is effectively undefined with built in language types?
谁真的需要一个内置语言类型有效未定义的运算符？
You asked for some real-world examples. Here's a spurious one. Let's say that you have a residential youth programme or similar, and one of the requirements is that the kids only share a room with someone of the same sex. You have a nullable M/F field in your database - nullable because your data feed is incomplete (you're still chasing down some of the data). Your room-matching code should definitely not match students where t1.Gender<=>t2.Gender, because it could end up matching two kids of unknown gender, who might be of opposite genders. Instead, you match where they're equal and not both null.
你问了一些现实世界的例子。这是一个虚假的。假设您有一个住宿青少年计划或类似计划，其中一个要求是，孩子们只与同性别的人共用一个房间。您的数据库中有一个可以为空的M / F字段 - 可以为空，因为您的数据提要不完整（您仍在追逐某些数据）。您的房间匹配代码绝对不能与t1.Gender <=> t2.Gender的学生匹配，因为它最终可能会匹配两个性别不明的孩子，他们可能是性别相反的。相反，你匹配它们相等的位置而不是空。
That's just one example. I admit that the behaviour of NULL and the = operator have caused a lot of confusion over the years, but ultimately the fault probably lies with the plethora of online MySQL tutorials that make no mention of how NULL interacts with operators, nor of the existence of the <=> operator.
这只是一个例子。我承认NULL和=运算符的行为多年来引起了很多混乱，但最终的错误可能在于过多的在线MySQL教程没有提到NULL如何与运算符交互，也没有提到存在<=>运算符。
#2
6
The big difference between null in mySQL and in programming languages is that in mySQL, null means unknown value while in programming it means undefined value.
mySQL和编程语言中null的最大区别在于，在mySQL中，null意味着未知值，而在编程中它意味着未定义的值。
In mySQL, null does not equal null (unknown does not equal unknown). While in programming languages, null does equal null (undefined equals undefined).
在mySQL中，null不等于null（unknown不等于unknown）。在编程语言中，null确实等于null（undefined等于undefined）。
#3
4
Who really needs an operator that is effectively undefined with built in language types?
谁真的需要一个内置语言类型有效未定义的运算符？
you also need it for relations inside your database. specially if you are using foreign key constraints.
你还需要它用于数据库内的关系。特别是如果您使用外键约束。
for example if you have a table for tasks (in your company). then you assign these tasks to employees. so you have a relation from your tasks-table to your employees-table.
and there will always be some unassigned tasks. in this case the field in your tasks-table you use for the relation to the employees table will contain NULL. this will make sure, that this task is unassigned. which means: there is no possibility that there is a relation to the employees table.
例如，如果您有一个任务表（在您的公司）。然后将这些任务分配给员工。所以你有从task-table到employees-table的关系。总会有一些未分配的任务。在这种情况下，您用于与employees表的关系的tasks-table中的字段将包含NULL。这将确保此任务未分配。这意味着：不可能与employees表有关系。
if NULL = NULL would be true, then in my example there would be always the possibility that the foreign key in the employees table also is NULL. thus the task would be assigned to one or some employees. and you never would be able to know for sure wheter a task is assigned to some employee or not.
如果NULL = NULL则为true，那么在我的示例中，employees表中的外键也总是为NULL。因此，任务将分配给一个或一些员工。并且您永远无法确定是否将任务分配给某个员工。
#4
2
Is there a reason MySql has both and not JUST the functionality in <=> ? The operators are completely different from each other.
有没有理由MySql同时具有<=>中的功能？运营商彼此完全不同。
<=> performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand is NULL.
<=>执行与=运算符相等的比较，但如果两个操作数均为NULL，则返回1而不是NULL;如果一个操作数为NULL，则返回0而不是NULL。
Who really needs an operator that is effectively undefined with built in language types?
谁真的需要一个内置语言类型有效未定义的运算符？
This depends on case, just because you haven't encountered such cases, does not mean nobody needs it.
这取决于案例，仅仅因为你没有遇到过这种情况，并不代表没有人需要它。
#5
1
Yes.
是。
This must be because relational databases use the theory of three-valued logic (TRUE, NULL, FALSE).
这必须是因为关系数据库使用三值逻辑理论（TRUE，NULL，FALSE）。
And the three-valued logic must work so because it must be internally consistent.
三值逻辑必须工作，因为它必须是内部一致的。
It follows from the rules of mathematics.
它遵循数学规则。
Comparisons with NULL and the three-valued logic
与NULL和三值逻辑的比较