这个操作符是什么<=>?在 MySQL 中? [英] What is this operator <=> in MySQL?
问题描述
我正在处理由以前的开发人员编写的代码，并且在查询中说，
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?
但它没有显示任何错误或异常.我已经知道 <> = != 在 MySQL.
But it is not showing any errors or exceptions. I already know that <> = != in MySQL.
推荐答案
TL;DR
这是 NULL安全等于运算符.
TL;DR
It's the NULL safe equal operator.
与常规的=运算符一样，比较两个值，结果是0(不相等)或1(相等)；换句话说:'a' <=>'b' 产生 0 和 'a' <=>'a' 产生 1.
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 0 and 'a' <=> 'a' yields 1.
与常规的 = 运算符不同，NULL 的值没有特殊含义，因此它永远不会产生 NULL 作为可能的结果;所以:'a' <=>NULL 产生 0 和 NULL <=>NULL 产生 1.
Unlike the regular = operator, values of NULL don't have a special meaning and so it never yields NULL as a possible outcome; so: 'a' <=> NULL yields 0 and NULL <=> NULL yields 1.
当两个操作数都可能包含 NULL 并且您需要两列之间的比较结果一致时，这会很有用.
This can come in useful when both operands may contain NULL and you need a consistent comparison result between two columns.
另一个用例是准备好的语句，例如:
Another use-case is with prepared statements, for example:
... WHERE col_a <=> ? ...
此处，占位符可以是标量值或 NULL，而无需更改查询的任何内容.
Here, the placeholder can be either a scalar value or NULL without having to change anything about the query.
除了<=>还有另外两个运算符可以用来与NULL进行比较，即IS NULL和<代码>不为空;它们是 ANSI 标准的一部分，因此在其他数据库上受支持，这与 <=> 不同，后者是 MySQL 特定的.
Besides <=> there are also two other operators that can be used to compare against NULL, namely IS NULL and IS NOT NULL; they're part of the ANSI standard and therefore supported on other databases, unlike <=>, which is MySQL-specific.
您可以将它们视为 MySQL <=> 的特化:
You can think of them as specialisations of MySQL's <=>:
'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
支持
SQL:2003 标准为此引入了一个谓词，其工作方式与 MySQL 的 <=> 操作符完全相同，形式如下:
Support
The SQL:2003 standard introduced a predicate for this, which works exactly like MySQL's <=> operator, in the following form:
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
这篇关于这个操作符是什么<=>?在 MySQL 中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
