Opera
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 <= and =>, 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 thanNULLif both operands areNULL, andrather thanNULLif 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 ofNULLdon't have a special meaning and so it never yieldsNULLas a possible outcome; so:'a' <=> NULLyieldsandNULL <=> NULLyields.
Contrary to=, whereby'a' = NULLyieldsNULLand evenNULL = NULLyieldsNULL; BTW, almost all operators and functions in MySQL work in this manner, because comparing againstNULLis basically undefined.
Usefulness
This is very useful for when both operands may containNULLand 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 orNULLwithout having to change anything about the query.
Related operators
Besides<=>there are also two other operators that can be used to compare againstNULL, namelyIS NULLandIS 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
相关标签:
本文原创发布php中文网,转载请注明出处,感谢您的尊重!