什么是运算符< =>在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和IS NOT 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屋！