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