MySQL中NULL值的使用应该注意这些点

mysql5.6

不能使用MySQL中的运算符如=，<，<>等和NULL做比较，因为任何和NULL做比较的运算都会得到NULL值结果，即无法通过运算比较符和NULL做比较得到有意义的结果。如下：

mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
+----------+-----------+----------+----------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
|     NULL |      NULL |     NULL |     NULL |
+----------+-----------+----------+----------+

而且，NULL值被认为是一个比任何数（除NULL）都要小的数，因此再用order by 字段的时候，NULL总是排在最后。

和NULL值做比较时，应该使用IS NULL或者IS NOT NULL：

mysql> SELECT 1 IS NULL, 1 IS NOT NULL;
+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
|         0 |             1 |
+-----------+---------------+

此外，NULL值只和它自身相等，即不等于0或者空字符串：

mysql> SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;
+-----------+---------------+------------+----------------+
| 0 IS NULL | 0 IS NOT NULL | '' IS NULL | '' IS NOT NULL |
+-----------+---------------+------------+----------------+
|         0 |             1 |          0 |              1 |
+-----------+---------------+------------+----------------+

因此，可以在NOT NULL列插入0或者’’，这也是一个优化点，NULL值比其他值难处理，所有很多情况下，我们可以将列设为NOT NULL，然后默认值为0或者’’。

小结

（1）

NULL值不等于0或者’’，因此不能认为下面的写法等价：

mysql> INSERT INTO my_table (phone) VALUES (NULL); 
mysql> INSERT INTO my_table (phone) VALUES ('');

（2）

NULL值和其他任何值做运算都会得到空值，包括NULL值和自己作运算。

mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
+----------+-----------+----------+----------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
|     NULL |      NULL |     NULL |     NULL |
+----------+-----------+----------+----------+

（3）

当列为NULL的时候，不要使用select XX from t where c1 = NULL；这类语句，因为这个语句的结果总是空。

（4）

当NULL值包含在运算表达式中时，得到的结果总是NULL，如下：

mysql> SELECT NULL, 1+NULL, CONCAT('Invisible',NULL);

这个sql语句中，1和’Invisible’都不是NULL，但是和NULL做运算，得到的结果将是NULL。

（5）

MIN(), and SUM()会忽略NULL值

（6）

使用not in和NULL比较时，如果子查询是null，则外查询结果是空集

mysql> select * from t;
+----+------+
| id | c    |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
|  5 |    2 |
|  6 |    2 |
|  7 |    2 |
|  8 |    2 |
|  9 | NULL |
+----+------+
9 rows in set (0.00 sec)

mysql> select * from t where c not in(null);
Empty set (0.00 sec)

（7）

使用 in和NULL比较时，无法查询出为NULL的记录

mysql> select * from t;
+----+------+
| id | c    |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
|  5 |    2 |
|  6 |    2 |
|  7 |    2 |
|  8 |    2 |
|  9 | NULL |
+----+------+
9 rows in set (0.00 sec)

mysql> select * from t where c in(null, 1);
+----+------+
| id | c    |
+----+------+
|  1 |    1 |
+----+------+
1 row in set (0.02 sec)

（8）
判断NULL只能使用IS NULL、IS NOT NULL

参考MySQL官网：https://dev.mysql.com/doc/refman/5.6/en/working-with-null.html