<=> operator in MySQL_MySQL
Have you ever seen "" in a SQL query while using MySQL? Does it mean less and equals to and greater than? Actually if you consider it as the union of , great, you are close to it. This is one form of equal operator in MySQL, it has the similar meaning to the = operator with some subtle difference.
According toMySQL documentation, isNULL
-safe equal. This operator performs an equality comparison like the=
operator, but returnsrather thanNULL
if both operands areNULL
, andrather thanNULL
if one operand isNULL.
For example:
mysql> SELECT 1 1, NULL NULL, 1 NULL;-> 1, 1, 0mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL;-> 1, NULL, NULL
Next let's get to know more details about operator in MySQL. To be noted this operator is not standard SQL operator, it's only available in MySQL.
Similarity with=
operator
Like the regular=
operator, two values are compared and the result is either(not equal) or(equal); in other words:'a' 'b'
yieldsand'a' 'a'
yields.
Difference with=
operator
Unlike the regular=
operator, values ofNULL
don't have a special meaning and so it never yieldsNULL
as a possible outcome; so:'a' NULL
yieldsandNULL NULL
yields.
Contrary to=
, whereby'a' = NULL
yieldsNULL
and evenNULL = NULL
yieldsNULL
; BTW, almost all operators and functions in MySQL work in this manner, because comparing againstNULL
is basically undefined.
Usefulness
This is very useful for when both operands may containNULL
and you need a consistent comparison result between two columns.
Another use-case is with prepared statements, for example:
...WHERE col_a ?...
Here, the placeholder can be either a scalar value orNULL
without having to change anything about the query.
Related operators
Besidesthere are also two other operators that can be used to compare against
NULL
, namelyIS NULL
andIS NOT NULL
; they're part of the ANSI standard and therefore supported on other databases, unlike, which is MySQL-specific.
You can think of them as specializations of MySQL's:
'a'ISNULL==>'a'NULL'a'ISNOTNULL==>NOT('a'NULL)
Based on this, your particular query (fragment) can be converted to the more portable:
WHERE p.name ISNULL
推荐阅读
-
【表格】大于号转义符>---小于号转义符<
-
Contest100000569 - 《算法笔记》2.5小节——C/C++快速入门-&amp;gt;数组
-
python如何提取js 内容 及遇到html转义符如何自动转义(pythoh3 ''&lt;abc&gt;' )
-
HTML转义字符,HTML字符实体< >: &
-
HTML<script>标签
-
C#List<T>排序多权重、自定义
-
.Net Core中使用ref和Span<T>提高程序性能
-
安装fc17后,mysql启动错误问题解决<转>
-
<转>mysql对于大表(千万级),要如何优化呢
-
MyBatis中 <sql>标签和<include>标签