mysql 中NULL被如何处理

1.enum字段与int字段有null情况
2.null字段在timestamp，datetime中的情况
3.select count在字段有null的情况
4.not in 和not exists在有null值存在的情况

举例：

1.enum字段与int字段有null情况
因为在MySQL中有三种状态：True、False、Unknown，任何NULL的比较操作都是Unknown状态，NULL跟任何比较操作返回结果都是unknown状态，而且所有查询条件都是unknown状态当做false处理。所以不会返回null这个组。

mysql> select default_group_type,count(*) from emailTemplateGroup where default_group_type <>'takedownTool' group by 1;
+--------------------+----------+
| default_group_type | count(*) |
+--------------------+----------+
| p2p                |      147 |
| Ad network         |      154 |
| searchEngine       |      154 |
| linkingsite        |      154 |
+--------------------+----------+
4 rows in set (0.00 sec)

mysql> select default_group_type,count(*) from emailTemplateGroup where default_group_type is null;
+--------------------+----------+
| default_group_type | count(*) |
+--------------------+----------+
| NULL               |      386 |
+--------------------+----------+
1 row in set (0.02 sec)

mysql> select 'p2p'<>'takedownTool','Ad network'<>'takedownTool','searchEngine' <>'takedownTool','linkingsite'<>'takedownTool','takedownTool'<>'takedownTool',null <>'takedownTool';
+-----------------------+------------------------------+---------------------------------+-------------------------------+--------------------------------+-----------------------+
| 'p2p'<>'takedownTool' | 'Ad network'<>'takedownTool' | 'searchEngine' <>'takedownTool' | 'linkingsite'<>'takedownTool' | 'takedownTool'<>'takedownTool' | null <>'takedownTool' |
+-----------------------+------------------------------+---------------------------------+-------------------------------+--------------------------------+-----------------------+
|                     1 |                            1 |                               1 |                             1 |                              0 |                  NULL |
+-----------------------+------------------------------+---------------------------------+-------------------------------+--------------------------------+-----------------------+

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

因为在MySQL中有三种状态：True、False、Unknown，任何NULL的比较操作都是Unknown状态，NULL跟任何比较操作返回结果都是unknown状态，而且所有查询条件都是unknown状态当做false处理。所以不会返回null这个组。
由此可见，我们不能对null进行数学运算，如要查询Null值数据必须在where后面加is null；

2. null字段在timestamp，datetime中的情况

timestamp 默认允许为 “非空”（not null by default）， 如果你在定义“ts TIMESTAMP DEFAULT NULL” 是非法的。 可以指定为空 null，当在timestamp中插入Null值，不管我们给timestamp定义default为何值，DB会默认记录当前时间

root@test 03:47:34>create table xbb4(
    -> id  int(10) unsigned NOT NULL AUTO_INCREMENT,
    -> col1 datetime,
    -> col2 timestamp,
    -> col3 date,
    -> PRIMARY KEY (`id`)
    -> )ENGINE=InnoDB  DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.33 sec)

root@test 03:57:29>insert into xbb4(col1,col2,col3) values(null,null,null);
Query OK, 1 row affected (0.00 sec)

root@test 03:57:36>select * from xbb4;
+----+------+---------------------+------+
| id | col1 | col2                | col3 |
+----+------+---------------------+------+
|  1 | NULL | 2013-05-23 07:57:36 | NULL |
+----+------+---------------------+------+
1 row in set (0.00 sec)

root@test 09:31:54>show create table xbb6\G
*************************** 1. row ***************************
       Table: xbb6
Create Table: CREATE TABLE `xbb6` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `col1` datetime DEFAULT '0000-00-00 00:00:00',
  `col2` timestamp DEFAULT '0000-00-00 00:00:00',
  `col3` date DEFAULT '0000-00-00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

root@test 09:32:25>insert into xbb6(col1,col2,col3) values(null,null,null);
Query OK, 1 row affected (0.00 sec)

root@test 09:32:51>select * from xbb6;
+----+------+---------------------+------+
| id | col1 | col2                | col3 |
+----+------+---------------------+------+
|  2 | NULL | 2013-05-24 01:32:51 | NULL |
+----+------+---------------------+------+
1 row in set (0.01 sec)


root@test 11:17:08>show create table xbb7\G
*************************** 1. row ***************************
       Table: xbb7
Create Table: CREATE TABLE `xbb7` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `col1` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `col2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `col3` date NOT NULL DEFAULT '0000-00-00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1


root@test 11:16:59>insert into xbb7(col2) values(null);
Query OK, 1 row affected (0.00 sec)

root@test 11:17:04>select * from xbb7;
+----+---------------------+---------------------+------------+
| id | col1                | col2                | col3       |
+----+---------------------+---------------------+------------+
|  5 | 0000-00-00 00:00:00 | 2013-05-24 03:17:04 | 0000-00-00 |
+----+---------------------+---------------------+------------+
2 rows in set (0.00 sec)

3.count在字段有null的情况下

COUNT(*)通常是对主键进行索引扫描，而COUNT(COL)就不一定了，另外前者是统计表中的所有符合的纪录总数，而后者是计算表中所有符合的COL的纪录数。null不被计数

root@test 11:27:01>select * from xbb6;
+----+------+---------------------+------+
| id | col1 | col2                | col3 |
+----+------+---------------------+------+
|  2 | NULL | 2013-05-24 03:20:57 | NULL |
+----+------+---------------------+------+
1 row in set (0.00 sec)

root@test 11:27:06>select count(*) from xbb6;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

root@test 11:27:13>select count(col1) from xbb6;
+-------------+
| count(col1) |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

root@test 11:27:19>select count(col2) from xbb6;
+-------------+
| count(col2) |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

也就是说，字段中有null值的，count(col) 将不被计数

4.not in 和not exists在有null值存在的情况
in 是子表驱动外表，把外表和那表作hash join，而exists是对外表作loop，外表驱动子表，每次loop再对那表进行查询。
由此可见使用not exists和not in要小心，当返回值中包含null时，not in返回false，not exists返回true。

root@test 11:36:28>select * from xbb;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 |    1 | NULL |
|  2 |    2 |    2 |
+----+------+------+
2 rows in set (0.00 sec)

root@test 11:36:39>select * from xbb2;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 |    2 |    2 |
+----+------+------+
1 row in set (0.00 sec)


root@test 11:37:40>select * from xbb where col2 not in (select col2 from xbb2);
Empty set (0.00 sec)


root@test 11:38:29>select * from xbb a where  not exists (select 1 from xbb2 b where a.col2 = b.col2);
+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 |    1 | NULL |
+----+------+------+
1 row in set (0.00 sec)