MySQL数据库，详解NULL让人防不胜防的坑(一)

当数据的值为NULL的时候，可能出现各种意想不到的效果，让⼈防不胜防，我们来看看

NULL导致的各种神坑，如何避免？

⽐较运算符中使⽤NULL

认真看下⾯的效果

mysql> select 1>NULL;

+--------+

| 1>NULL |

+--------+

| NULL |

+--------+

1 row in set (0.00 sec)

mysql> select 1<NULL;

这是Mysql系列第13篇。

环境：mysql5.7.25，cmd命令中进⾏演⽰。+--------+

| 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。

准备数据

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的记录。