MySQL 中的这个运算符 <=> 是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21927117/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is this operator <=> in MySQL?
提问by zzlalani
I'm working on code written by a previous developer and in a query it says,
我正在处理由以前的开发人员编写的代码，并且在查询中说，
WHERE p.name <=> NULL
What does <=>mean in this query? Is it something equal to =? Or is it a syntax error?
<=>这个查询是什么意思？它等于=什么？还是语法错误？
But it is not showing any errors or exceptions. I already know that <>= !=in MySQL.
但它没有显示任何错误或异常。我已经知道<>=!=在MySQL的。
回答by Ja?ck
TL;DR
TL; 博士
It's the NULLsafe equaloperator.
它是NULL安全的相等运算符。
Like the regular =operator, two values are compared and the result is either 0(not equal) or 1(equal); in other words: 'a' <=> 'b'yields 0and 'a' <=> 'a'yields 1.
与常规=运算符一样，比较两个值，结果是0（不等于）或1（等于）；换句话说：'a' <=> 'b'yields0和'a' <=> 'a'yields 1。
Unlike the regular =operator, values of NULLdon't have a special meaning and so it never yields NULLas a possible outcome; so: 'a' <=> NULLyields 0and NULL <=> NULLyields 1.
与常规=运算符不同，值NULL没有特殊含义，因此它永远不会NULL作为可能的结果产生；所以：'a' <=> NULL产量0和NULL <=> NULL产量1。
Usefulness
用处
This can come in useful when both operands may contain NULLand you need a consistent comparison result between two columns.
当两个操作数都可能包含NULL并且您需要两列之间的比较结果一致时，这会很有用。
Another use-case is with prepared statements, for example:
另一个用例是准备好的语句，例如：
... WHERE col_a <=> ? ...
Here, the placeholder can be either a scalar value or NULLwithout having to change anything about the query.
在这里，占位符可以是标量值，也可以NULL不必更改有关查询的任何内容。
Related operators
相关运算符
Besides <=>there are also two other operators that can be used to compare against NULL, namely IS NULLand IS NOT NULL; they're part of the ANSI standard and therefore supported on other databases, unlike <=>, which is MySQL-specific.
此外<=>还有另外两个运算符可用于比较NULL，即IS NULL和IS NOT NULL；它们是 ANSI 标准的一部分，因此在其他数据库上受支持<=>，这与 MySQL 特定的 不同。
You can think of them as specialisations of MySQL's <=>:
您可以将它们视为 MySQL 的专业化<=>：
'a' IS NULL ==> 'a' <=> NULL
'a' IS NOT NULL ==> NOT('a' <=> NULL)
Based on this, your particular query (fragment) can be converted to the more portable:
基于此，您的特定查询（片段）可以转换为更便携的：
WHERE p.name IS NULL
Support
支持
The SQL:2003 standard introduced a predicate for this, which works exactly like MySQL's <=>operator, in the following form:
SQL:2003 标准为此引入了一个谓词，它的工作方式与 MySQL 的<=>运算符完全相同，格式如下：
IS [NOT] DISTINCT FROM
The following is universally supported, but is relative complex:
以下是普遍支持的，但相对复杂：
CASE WHEN (a = b) or (a IS NULL AND b IS NULL)
THEN 1
ELSE 0
END = 1
回答by Drixson Ose?a
is <=>NULL-safe equal to operator
是<=>NULL-safe equal to operator
This operator 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。
See here for the documentation
有关文档，请参阅此处
Sample :
样本 ：
you should use IS NOT NULL. (The comparison operators = and <> both give UNKNOWN with NULL on either side of the expression.)
你应该使用 IS NOT NULL。（比较运算符 = 和 <> 都在表达式的任一侧给出 UNKNOWN 和 NULL。）
SELECT *
FROM table
WHERE YourColumn IS NOT NULL;
can also negate the null safe equality operator but this is not standard SQL.
也可以否定空安全相等运算符，但这不是标准 SQL。
SELECT *
FROM table
WHERE NOT (YourColumn <=> NULL);
回答by Rahul Tripathi
It is the NULL-safe equal to operator
<=> Operator is used to compare NULL values with the fields. If normal =(equals) Operators return NULL if one of the comparison value is NULL. With <=> operator returns true or false. <=> Operator is same as IS NULL.
<=> 运算符用于将 NULL 值与字段进行比较。如果正常 =(equals) 如果比较值之一为 NULL，则运算符返回 NULL。使用 <=> 运算符返回 true 或 false。<=> 运算符与 IS NULL 相同。
From the manual:-
从手册： -
<=>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。
mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
-> 1, 1, 0
mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL;
-> 1, NULL, NULL
Edit:-(Although very late to add one important side note mentioning NOT <=>as well)
编辑： - （虽然很晚添加一个重要侧面说明提不<=>以及）
On a side note:-
附注：-
NOT <=>
不是 <=>
There is one more point NOT <=>which is used to compare NULL values with the fields. If normal != or <> (not equals) Operators return NULL if one of the comparison value is NULL. With NOT applied to <=> operator returns true or false. NOT applied to <=> Operator is same as IS NOT NULL.
还有一点NOT <=>用于将 NULL 值与字段进行比较。如果正常 != 或 <> (不等于) 如果比较值之一为 NULL，则运算符返回 NULL。将 NOT 应用于 <=> 运算符返回 true 或 false。NOT 应用于 <=> 运算符与 IS NOT NULL 相同。
Example:-
例子：-
SELECT NULL != NULL, //--Result is NULL
NOT NULL <=> NULL, //--Result is 0
NULL IS NOT NULL; //--Result is 0
回答by elixenide
<=>is MySQL's null-safe "equal to" operator. From the manual:
<=>是 MySQL 的空安全“等于”运算符。 从手册：
NULL-safe equal. This operator 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 安全的相等。此运算符执行与 = 运算符类似的相等比较，但如果两个操作数均为 NULL，则返回 1 而不是 NULL，如果一个操作数为 NULL，则返回 0 而不是 NULL。
mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
-> 1, 1, 0
mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL;
-> 1, NULL, NULL
回答by Dimag Kharab
NULL-safe equal. This operator 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 安全的相等。此运算符执行与 = 运算符类似的相等比较，但如果两个操作数均为 NULL，则返回 1 而不是 NULL，如果一个操作数为 NULL，则返回 0 而不是 NULL。
mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
-> 1, 1, 0
mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL;
-> 1, NULL, NULL
It significance:
它的意义：
When you compare a NULL value with a non-NULL value, you'll get NULL. If you want to check if a value is null.
当您将 NULL 值与非 NULL 值进行比较时，您将得到 NULL。如果你想检查一个值是否为空。
The Equality operator(<=>) which considers NULL as a normal value, so it returns 1 (not NULL) if both values are NULL and returns 0 (not NULL) if one of the values is NULL:
相等运算符 (<=>) 将 NULL 视为正常值，因此如果两个值都为 NULL，则返回 1（非 NULL），如果其中一个值为 NULL，则返回 0（非 NULL）：
eg
例如
SELECT NULL <=> NULL -- 1
SELECT TRUE <=> TRUE -- 1
SELECT col1 <=> col2 FROM myTable
回答by Salman A
<=>is the NULL-safe equal operator. a <=> bis same as writing:
<=>是NULL 安全的等号运算符。a <=> b与写作相同：
CASE
WHEN a IS NULL AND b IS NULL THEN 1 -- both operands null then 1
WHEN a IS NULL OR b IS NULL THEN 0 -- one operand is null then 0
ELSE a = b -- else behave like normal = operator
END
And sorry, I could not find one good reason to use this operator instead of AND/OR IS (NOT) NULL. Your example for example, WHERE p.name <=> NULLis same as WHERE p.name IS NULL.
抱歉，我找不到使用此运算符代替AND/OR IS (NOT) NULL. 例如，您的示例WHERE p.name <=> NULL与WHERE p.name IS NULL.
回答by Jojodmo
From the MySQL documentation:
从MySQL 文档：
NULL-safe equal. This operator 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 安全的相等。此运算符执行与 = 运算符类似的相等比较，但如果两个操作数均为 NULL，则返回 1 而不是 NULL，如果一个操作数为 NULL，则返回 0 而不是 NULL。
An example using the <=>operator would be:
使用<=>运算符的一个例子是：
SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
Which would return:
哪个会返回：
1, 1, 0
An example of the regular =operator would be:
常规=运算符的一个例子是：
SELECT 1 = 1, NULL = NULL, 1 = NULL;
Which would return:
哪个会返回：
1, NULL, NULL
The <=>operator is very similar to the =operator, except <=>will never return NULL
该<=>操作是非常相似的=操作，除了<=>将永远不会返回NULL
回答by MusicLovingIndianGirl
It is the NULL - Safe Equal to operator. Check description.
它是 NULL - Safe Equal to 运算符。检查说明。
回答by zloctb
mysql> SELECT * FROM t JOIN t2 WHERE t2.ids = t.ids;
+----+------+----+------+
| id | ids | id | ids |
+----+------+----+------+
| 1 | 1 | 1 | 1 |
| 2 | 2 | 2 | 2 |
| 5 | 6 | 5 | 6 |
| 6 | 7 | 6 | 7 |
+----+------+----+------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM t JOIN t2 WHERE t2.ids <=> t.ids;
+----+------+----+------+
| id | ids | id | ids |
+----+------+----+------+
| 1 | 1 | 1 | 1 |
| 2 | 2 | 2 | 2 |
| 3 | NULL | 3 | NULL |
| 4 | NULL | 3 | NULL |
| 3 | NULL | 4 | NULL |
| 4 | NULL | 4 | NULL |
| 5 | 6 | 5 | 6 |
| 6 | 7 | 6 | 7 |
