我们都知道,null值不能用在等值运算,也不能用来比较大小。道理都懂,但是在实际SQL中,你真的注意到了吗?
例如,我有如下一个tab表,共有5条数据,其中name='sam’有2条数据:
mysql> desc tab;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| col1 | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> select count(*) from tab;
+----------+
| count(*) |
+----------+
| 5 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from tab where name='sam';
+----------+
| count(*) |
+----------+
| 2 |
+------