mysql 使用不等于 ‘＜ ＞’ 过滤 null 的问题

mysql 使用不等于 ‘< >’ 过滤 null 的问题

没有废话、直接上示例

drop table if exists testa;
create table testa(
    id int,
    time_status varchar(8)
);
insert into testa values (1,'25631001');
insert into testa values (2,null);
insert into testa values (3,'25631002');
insert into testa values (4,'25631002');
insert into testa values (5,'25631003');

1. 如果给定我们的条件是 输出所有 time_status 不为 25631002 的所有值，我是用如下方式写的,如果你是搜到这边文章看的话，我相信你也是这样写的

   select * from testa where time_status <> '25631002';
   

   但是输出结果只有 id=1 和 id=5 的值，如下:

   mysql> select * from testa where time_status <> 25631002;
   +------+-------------+
   | id   | time_status |
   +------+-------------+
   |    1 | 25631001    |
   |    5 | 25631003    |
   +------+-------------+
   2 rows in set (0.00 sec)
   

2. 处理方式

   • select * from testa where time_status <> 25631002 or time_status is null;

   • select * from testa where ifnull(time_status,'') <> 25631002;

     mysql> select * from testa where ifnull(time_status,'') <> 25631002;
     +------+-------------+
     | id   | time_status |
     +------+-------------+
     |    1 | 25631001    |
     |    2 | NULL        |
     |    5 | 25631003    |
     +------+-------------+
     3 rows in set (0.00 sec)
     

这是因为null空值，表示没有值，即不满足值不等于 25631002 的条件,所以会被剔除