Mysql NULL导致的神坑

吾爱主题阅读：165 2024-04-01 23:50:01 评论：0

比较运算符中使用NULL

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 mysql> select 1> NULL ; + --------+ | 1> NULL | + --------+ | NULL | + --------+ 1 row in set (0.00 sec) mysql> select 1< NULL ; + --------+ | 1< NULL | + --------+ | NULL | + --------+ 1 row in set (0.00 sec) mysql> select 1<> NULL ; + ---------+ | 1<> NULL | + ---------+ | NULL | + ---------+ 1 row in set (0.00 sec) mysql> select 1> NULL ; + --------+ | 1> NULL | + --------+ | NULL | + --------+ 1 row in set (0.00 sec) mysql> select 1< NULL ; + --------+ | 1< NULL | + --------+ | NULL | + --------+ 1 row in set (0.00 sec) mysql> select 1>= NULL ; + ---------+ | 1>= NULL | + ---------+ | NULL | + ---------+ 1 row in set (0.00 sec) mysql> select 1<= NULL ; + ---------+ | 1<= NULL | + ---------+ | NULL | + ---------+ 1 row in set (0.00 sec) mysql> select 1!= NULL ; + ---------+ | 1!= NULL | + ---------+ | NULL | + ---------+ 1 row in set (0.00 sec) mysql> select 1<> NULL ; + ---------+ | 1<> NULL | + ---------+ | NULL | + ---------+ 1 row in set (0.00 sec) mysql> select NULL = NULL , NULL != NULL ; + -----------+------------+ | NULL = NULL | NULL != NULL | + -----------+------------+ | NULL | NULL | + -----------+------------+ 1 row in set (0.00 sec) mysql> select 1 in ( null ),1 not in ( null ), null in ( null ), null not in ( null ); + -------------+-----------------+----------------+--------------------+ | 1 in ( null ) | 1 not in ( null ) | null in ( null ) | null not in ( null ) | + -------------+-----------------+----------------+--------------------+ | NULL | NULL | NULL | NULL | + -------------+-----------------+----------------+--------------------+ 1 row in set (0.00 sec) mysql> select 1= any ( select null ), null = any ( select null ); + --------------------+-----------------------+ | 1= any ( select null ) | null = any ( select null ) | + --------------------+-----------------------+ | NULL | NULL | + --------------------+-----------------------+ 1 row in set (0.00 sec) mysql> select 1= all ( select null ), null = all ( select null ); + --------------------+-----------------------+ | 1= all ( select null ) | null = all ( select null ) | + --------------------+-----------------------+ | NULL | NULL | + --------------------+-----------------------+ 1 row in set (0.00 sec)

结论：任何值和NULL使用运算符（>、<、>=、<=、!=、<>）或者（in、not in、any/some、all）比较时，返回值都为NULL，NULL作为布尔值的时候，不为1也不为0。

准备数据

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 mysql> create table test1(a int ,b int ); Query OK, 0 rows affected (0.01 sec) mysql> insert into test1 values (1,1),(1, null ),( null , null ); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from test1; + ------+------+ | a | b | + ------+------+ | 1 | 1 | | 1 | NULL | | NULL | NULL | + ------+------+ 3 rows in set (0.00 sec)

上面3条数据，认真看一下，特别是注意上面NULL的记录。

IN、NOT IN和NULL比较

IN和NULL比较

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 mysql> select * from test1; + ------+------+ | a | b | + ------+------+ | 1 | 1 | | 1 | NULL | | NULL | NULL | + ------+------+ 3 rows in set (0.00 sec) mysql> select * from test1 where a in ( null ); Empty set (0.00 sec) mysql> select * from test1 where a in ( null ,1); + ------+------+ | a | b | + ------+------+ | 1 | 1 | | 1 | NULL | + ------+------+ 2 rows in set (0.00 sec)

结论：当IN和NULL比较时，无法查询出为NULL的记录。

NOT IN 和NULL比较

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 mysql> select * from test1 where a not in (1); Empty set (0.00 sec) mysql> select * from test1 where a not in ( null ); Empty set (0.00 sec) mysql> select * from test1 where a not in ( null ,2); Empty set (0.00 sec) mysql> select * from test1 where a not in (2); + ------+------+ | a | b | + ------+------+ | 1 | 1 | | 1 | NULL | + ------+------+ 2 rows in set (0.00 sec)

结论：当NOT IN 后面有NULL值时，不论什么情况下，整个sql的查询结果都为空。

EXISTS、NOT EXISTS和NULL比较

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 mysql> select * from test2; + ------+------+ | a | b | + ------+------+ | 1 | 1 | | 1 | NULL | | NULL | NULL | + ------+------+ 3 rows in set (0.00 sec) mysql> select * from test1 t1 where exists ( select * from test2 t2 where t1.a = t2.a); + ------+------+ | a | b | + ------+------+ | 1 | 1 | | 1 | NULL | + ------+------+ 2 rows in set (0.00 sec) mysql> select * from test1 t1 where not exists ( select * from test2 t2 where t1.a = t2.a); + ------+------+ | a | b | + ------+------+ | NULL | NULL | + ------+------+ 1 row in set (0.00 sec)

上面我们复制了表test1创建了表test2。

查询语句中使用exists、not exists对比test1.a=test2.a，因为=不能比较NULL，结果和预期一致。

判断NULL只能用IS NULL、IS NOT NULL

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 mysql> select 1 is not null ; + ---------------+ | 1 is not null | + ---------------+ | 1 | + ---------------+ 1 row in set (0.00 sec) mysql> select 1 is null ; + -----------+ | 1 is null | + -----------+ | 0 | + -----------+ 1 row in set (0.00 sec) mysql> select null is null ; + --------------+ | null is null | + --------------+ | 1 | + --------------+ 1 row in set (0.00 sec) mysql> select null is not null ; + ------------------+ | null is not null | + ------------------+ | 0 | + ------------------+ 1 row in set (0.00 sec)

看上面的效果，返回的结果为1或者0。

结论：判断是否为空只能用IS NULL、IS NOT NULL。

聚合函数中NULL的坑

示例

1 2 3 4 5 6 7 mysql> select count (a), count (b), count (*) from test1; + ----------+----------+----------+ | count (a) | count (b) | count (*) | + ----------+----------+----------+ | 2 | 1 | 3 | + ----------+----------+----------+ 1 row in set (0.00 sec)

count(a)返回了2行记录，a字段为NULL的没有统计出来。
count(b)返回了1行记录，为NULL的2行记录没有统计出来。
count(*)可以统计所有数据，不论字段的数据是否为NULL。

再继续看

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 mysql> select * from test1 where a is null ; + ------+------+ | a | b | + ------+------+ | NULL | NULL | + ------+------+ 1 row in set (0.00 sec) mysql> select count (a) from test1 where a is null ; + ----------+ | count (a) | + ----------+ | 0 | + ----------+ 1 row in set (0.00 sec)

上面第1个sql使用is null查询出了结果，第2个sql中count(a)返回的是0行。

结论：count(字段)无法统计字段为NULL的值，count(*)可以统计值为null的行。

NULL不能作为主键的值

1 2 3 4 5 mysql> create table test3(a int primary key ,b int ); Query OK, 0 rows affected (0.01 sec) mysql> insert into test3 values ( null ,1); ERROR 1048 (23000): Column 'a' cannot be null

上面我们创建了一个表test3，字段a未指定不能为空，插入了一条NULL的数据，报错原因：a 字段的值不能为NULL，我们看一下表的创建语句：

1 2 3 4 5 6 7 8 9 10 11 mysql> show create table test3; + -------+------------+ | Table | Create Table | + -------+------------+ | test3 | CREATE TABLE `test3` ( `a` int (11) NOT NULL , `b` int (11) DEFAULT NULL , PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 + -------+------------+ 1 row in set (0.00 sec)