Mysql选择行，其中两列不具有相同的值

本文介绍了Mysql选择行，其中两列不具有相同的值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试运行一个查询，其中两列不相同，但是没有返回任何结果:

I'm trying to run a query where two columns are not the same, but it's not returning any results:

SELECT * FROM `my_table` WHERE `column_a` != `column_b`;

column_a和column_b是整数类型，可以包含空值.我试过使用<> IS NOT等等，没有任何运气.使用< =>可以很容易地找到它们是否相同，但是<>和！=不会返回任何行. (使用Mysql 5.0).

column_a AND column_b are of integer type and can contain nulls. I've tried using <> IS NOT, etc without any luck. It's easy to find if they're the same using <=>, but <> and != doesn't return any rows. (using Mysql 5.0).

有想法吗?

推荐答案

问题是当a或b为NULL时，a！= b为NULL.

The problem is that a != b is NULL when either a or b is NULL.

<=> 是NULL安全等于运算符.要获得不等于NULL的安全值，您可以简单地将结果取反:

<=> is the NULL-safe equals operator. To get a NULL-safe not equal to you can simply invert the result:

SELECT * FROM my_table WHERE NOT column_a <=> column_b

如果不使用null安全运算符，则必须执行以下操作:

Without using the null safe operator you would have to do this:

SELECT * FROM my_table WHERE column_a != column_b OR (column_a IS NULL AND column_b IS NOT NULL) OR (column_b IS NULL AND column_a IS NOT NULL)