SQL：各种NULL相关的总结

柔情只为你懂 2022-03-26 04:22 183阅读 0赞

### 文章目录 ###

*  NULL 与count
 *  包含NULL值的NOT IN
 *  ifnull

# NULL 与count #

mysql> create table tb_1(
    id int unsigned not null auto_increment,
    c1 varchar(200) default null,
    c2 int not null,
    primary key(id)
    );
    
    mysql> insert into tb_1(c1, c2) values(NULL, 1),(1,2),(null, 3);
    
    mysql> select * from tb_1;
    +----+------+----+
    | id | c1   | c2 |
    +----+------+----+
    |  1 | NULL |  1 |
    |  2 | 1    |  2 |
    |  3 | NULL |  3 |
    +----+------+----+
    
    mysql> select count(c1), count(*), count(1), count(c2), count(id) from tb_1;
    +-----------+----------+----------+-----------+-----------+
    | count(c1) | count(*) | count(1) | count(c2) | count(id) |
    +-----------+----------+----------+-----------+-----------+
    |         3 |        3 |        3 |         3 |         3 |
    +-----------+----------+----------+-----------+-----------+

# 包含NULL值的NOT IN #

mysql> select null in ('a', 'b', null);
    +--------------------------+
    | null in ('a', 'b', null) |
    +--------------------------+
    |                     NULL |
    +--------------------------+
    1 row in set (0.00 sec)

> MySQL数据库的`比较操作`，除了返回`1(True)`, `0(False)`之外，还会返回`NULL`  
> `NULL`和`NULL`的比较，返回的还是`NULL`

mysql> select null not in ('a', 'b', null);  
    +------------------------------+
    | null not in ('a', 'b', null) |
    +------------------------------+
    |                         NULL |  -- null不在('a', 'b', null)中，返回的还是null，因为有null和null的比较
    +------------------------------+
    
    
    mysql> select 'a' in ('a', 'b', null);
    +-------------------------+
    | 'a' in ('a', 'b', null) |
    +-------------------------+
    |                       1 |
    +-------------------------+
    1 row in set (0.00 sec)
    
    mysql> select 'a' not in ('a', 'b', null);
    +-----------------------------+
    | 'a' not in ('a', 'b', null) |
    +-----------------------------+
    |                           0 | -- a 在 ('a', 'b', null)中，返回0,即False
    +-----------------------------+
    
    mysql> select 'c' in ('a', 'b', null);
    +-------------------------+
    | 'c' in ('a', 'b', null) |
    +-------------------------+
    |                    NULL |
    +-------------------------+
    
    mysql> select 'c' not in ('a', 'b', null);
    +-----------------------------+
    | 'c' not in ('a', 'b', null) |
    +-----------------------------+
    |                        NULL |  -- 理论上应该是返回1，即True的。但是包含了null值。则返回null
    +-----------------------------+
    
    mysql> select 'c' not in ('a', 'b');
    +-----------------------+
    | 'c' not in ('a', 'b') |
    +-----------------------+
    |                     1 |  -- 这个返回值可以理解 'c'不在('a', 'b')中，返回1，即为True
    +-----------------------+

> **对于包含了`NULL`值的`IN`操作，总是返回`True`或者`NULL`**  
> **`NOT IN`返回`NOT True (False)`或者`NOT NULL (NULL)`**

*  NULL与比较运算符

# ifnull #

mysql> select @test;
    +-------+
    | @test |
    +-------+
    | NULL  |  -- 当前会话中没有test变量
    +-------+
    1 row in set (0.00 sec)
    
    mysql> select ifnull(@test, 100);   -- 如果test为NULL，则ifnull返回100
    +--------------------+
    | ifnull(@test, 100) |
    +--------------------+
    | 100                |  -- ifnull函数return的值是100
    +--------------------+
    1 row in set (0.00 sec)
    
    mysql> select @test;
    +-------+
    | @test |
    +-------+
    | NULL  |  -- 但是test还是NULL
    +-------+
    1 row in set (0.00 sec)
    
    mysql> set @test:=200;  -- 给test变量赋值为200
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select ifnull(@test, 100);  -- 再次ifnull判断，此时test不为null，则返回test变量的值
    +--------------------+
    | ifnull(@test, 100) |
    +--------------------+
    |                200 |  -- test不为null。返回test的值200
    +--------------------+
    1 row in set (0.00 sec)
    ``