MySql Null注意事项
在mysql中使用null值时有很多注意事项,踩过不少坑,在此总结以下。(IFNULL()函数可以将NULL值转换成你想要的值,用法为:IFNULL(expr1,expr2))
计算
mysql中所有与null计算的结果均为null
mysql> SELECT NULL, 1+NULL,1-NULL,1*NULL,1/NULL, CONCAT('Invisible',NULL),SUBSTRING('12345',1,NULL),REPLACE('abc','c',NULL);
+------+--------+--------+--------+--------+--------------------------+---------------------------+-------------------------+
| NULL | 1+NULL | 1-NULL | 1*NULL | 1/NULL | CONCAT('Invisible',NULL) | SUBSTRING('12345',1,NULL) | REPLACE('abc','c',NULL) |
+------+--------+--------+--------+--------+--------------------------+---------------------------+-------------------------+
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+------+--------+--------+--------+--------+--------------------------+---------------------------+-------------------------+
1 row in set (0.00 sec)
统计
当使用DISTINCT, GROUP BY, 或者 ORDER BY 时所有的NULL值都看作相等的。
当使用一些统计函数时,mysql会忽略NUll的值,比如count(),min(),sum().
创建测试表a,并插入如下数据:
mysql> select * from a;
+----+------+
| id | t |
+----+------+
| 1 | NULL |
| 2 | 1 |
| 3 | 2 |
+----+------+
3 rows in set (0.00 sec)
执行如下结果,查看值:
mysql> select count(*),count(t),sum(t),min(t) from a;
+----------+----------+--------+--------+
| count(*) | count(t) | sum(t) | min(t) |
+----------+----------+--------+--------+
| 3 | 2 | 3 | 1 |
+----------+----------+--------+--------+
1 row in set (0.00 sec)
从结果可以看出,在使用count(t)时没有将null的值计算在内;使用sum(t)时也不是以1+2+null来计算的,而是忽略了NULL的值。
排序
在使用order by对存在NULL值的列进行排序时,在ASC情况下NULL值会排在前面,使用DESC时NULL会排在后面。
以上面的表为例:
mysql> select * from a order by t;
+----+------+
| id | t |
+----+------+
| 1 | NULL |
| 2 | 1 |
| 3 | 2 |
+----+------+
3 rows in set (0.00 sec)
mysql> select * from a order by t desc;
+----+------+
| id | t |
+----+------+
| 3 | 2 |
| 2 | 1 |
| 1 | NULL |
+----+------+
3 rows in set (0.00 sec)
在oracle中NULL值排序和mysql一样,但是可以使用nulls first,nulls last来控制排序前后。
比较
在对null值进行比较时,只能使用 is 、not is、<=>来进行,而不能使用=、<>、>、<等运算符号。mysql中空字符串和NULL值不是等价的。
在a表中插入一条新值:
mysql> insert into a values (NULL,'');
Query OK, 1 row affected (0.06 sec)
再查看如下语句:
mysql> select * from a where t = null;
Empty set (0.00 sec)
mysql> select * from a where t is null;
+----+------+
| id | t |
+----+------+
| 1 | NULL |
+----+------+
1 row in set (0.00 sec)
mysql> select * from a where t = '';
+----+------+
| id | t |
+----+------+
| 4 | |
+----+------+
1 row in set (0.00 sec)
mysql> select * from a where t <=> null;
+----+------+
| id | t |
+----+------+
| 1 | NULL |
+----+------+
1 row in set (0.00 sec)
插入
从上面一个例子中的插入语句我们可以看到,在insert语句中插入NULL值时,实际结果不一定是NULL。
mysql在以下两种情况下,插入NULL值时,实际插入的结果不为NULL:
向自增(AUTO_INCREMENT)字段中插入NULL值,实际插入的是自增的下一个值。
向 TIMESTAMP字段中插入NULL值,实际插入的是当前时间。