MySQL - NULL安全不等于运算符

I am just curious. I know about NULL safe equal operator <=>, but is there some NULL safe NOT equal operator, or I have to always use something like that:

我只是好奇。我知道NULL安全等于运算符<=>，但是有一些NULL安全NOT等于运算符，或者我必须总是使用类似的东西：

(tab.id != 1 OR tab.id IS NULL)

or someone prefers

或者有人喜欢

!(tab.id <=> 1)

4 个解决方案

#1

COALESCE(tab.id, 0) != 1

Can be used here if you like it. I goes through the parameters, and returns the first value that isn't NULL. In this case if it's NULL, it will compare 0 != 1. Although it may use more signs, it's still easier to manage instead of being forced to always have opposite "booleans" as a solution in those cases.

如果你喜欢的话可以在这里使用。我遍历参数，并返回非NULL的第一个值。在这种情况下，如果它为NULL，它将比较0！= 1.虽然它可能使用更多的符号，但它仍然更容易管理，而不是*在这些情况下总是使用相反的“布尔”作为解决方案。

Read documentation for COALESCE()

阅读COALESCE（）的文档

#2

I found that NOT (NULL <=> 1) works and I think it is also ISO standard compliant, but is cumbersome. A better way to show using column names would be like this: NOT (tbl.col1 <=> 1)

我发现NOT（NULL <=> 1）有效，我认为它也符合ISO标准，但很麻烦。使用列名显示的更好方法是：NOT（tbl.col1 <=> 1）

#3

Now MySQL does not have a NULL-safe not equal operator.

现在MySQL没有NULL安全不等于运算符。

Using MySQL the most universal solution is:

使用MySQL最通用的解决方案是：

!(tab.id <=> 1)

要么

NOT tab.id <=> 1

because it will work properly if even in place of 1 you will use NULL.

因为它会正常工作，即使代替1，你也会使用NULL。

#4

If you know that the RHS of the comparison IS NOT NULL:

如果您知道比较的RHS不是NULL：

COALESCE(tab.id != 1, 1)

要么

COALESCE(tab.id != 1, TRUE)

will give you the correct result.

会给你正确的结果。

Whether this is better more readable than:

这是否更具可读性：

(tab.id != 1 OR tab.id IS NULL)

Is debatable..

有争议..

I'd also be tempted to filter out the NULL logic first with comparisons, so I don't have to think about them! I'd probably write your first equation as:

我也很想通过比较来过滤掉NULL逻辑，所以我不必考虑它们！我可能会把你的第一个等式写成：

(tab.id IS NULL OR tab.id != 1)

Performance will depend on prevalence of NULLs however.

然而，性能将取决于NULL的普遍性。

#1

COALESCE(tab.id, 0) != 1