SQL：各种NULL相关的总结

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与比较运算符

--
-- 和 null比较，使用is和is not， 而不是 = 和 <>
--
mysql> select null = null; 
+-------------+
| null = null |
+-------------+
|        NULL |
+-------------+

mysql> select null <> null;
+--------------+
| null <> null |
+--------------+
|         NULL |
+--------------+

mysql> select null is null; 
+--------------+
| null is null |
+--------------+
|            1 |  -- 返回 True
+--------------+

mysql> select null is not  null;
+-------------------+
| null is not  null |
+-------------------+
|                 0 |  -- 返回 False
+-------------------+

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)
``