mysql中空值的处理
2014-03-26 15:25:24 
阿炯
在查询语句中需要对字段的内容是否为空的情况进行判断，本文总结了几种'空'的情况，以供参考。简单地讲，为null或''的情况就是为空，使用=的null值对比是错误的，可以以下面几种方式出现：
WHERE some_col IS NULL OR some_col = '';
WHERE MyCol IS NULL;
WHERE NULLIF(some_col, '') IS NULL;
WHERE IS NOT NULL;
'' = ' ' IS TRUE
'' = ' ' IS TRUE
' ' = ' ' IS TRUE
' ' = ' ' IS TRUE
SELECT 0 IS NULL , '' IS NULL , NULL IS NULL
-> 0, 0, 1
或者
SELECT ISNULL(' ') , ISNULL( NULL )
-> 0 ,1
'null'系列函数在使用上有一些区别，具体解析如下。
isnull,ifnull,nullif的用法如下：
isnull(expr)的用法：
如expr为null，那么isnull()的返回值为1，否则返回值为0。
mysql> select isnull(1+1);
-> 0
mysql> select isnull(1/0);
-> 1
isnull()函数同'is null'比较操作符具有一些相同性。
IFNULL(expr1,expr2)的用法：
假如expr1不为NULL，则IFNULL()的返回值为expr1，否则其返回值为expr2。IFNULL()的返回值是数字或是字符串，具体情况取决于其所使用的语境。这与Perl中的三元运算在本质是相同的。
mysql>SELECT IFNULL(1,0);
->1
mysql>SELECT IFNULL(NULL,10);
->10
mysql>SELECT IFNULL(1/0,10);
->10
mysql>SELECT IFNULL(1/0,’yes’);
->‘yes’
IFNULL(expr1,expr2)的默认结果值为两个表达式中更加“通用”的一个，顺序为STRING、REAL或INTEGER。假设一个基于表达式的表的情况，或MySQL必须在内存中储存一个临时表中IFNULL()的返回值：
CREAT TABLE freeoa SELECT IFNULL(1,’test’) AS test；
NULLIF(expr1,expr2)的用法：
如果'expr1=expr2'成立，那么返回值为NULL，否则返回值为expr1。这和'CASE WHEN expr1=expr2 THEN NULL ELSE expr1 END'相同。
mysql>SELECT NULLIF(1,1);
->NULL
mysql>SELECT NULLIF(1,2);
->1
如果参数不相等，则MySQL两次求得的值为expr1。
COUNT(*)和COUNT(1)
COUNT(1)中的1并不是指第一列，SELECT 1 FROM table会将表中的每一行都返回常量1，有多少行返回多少行个'1'。
COUNT(*)会统计所有行数，COUNT(expression)会统计符合表达式的非null值，COUNT(column_name)会统计对应列中非null值。由于0和1都是非null值，所以COUNT(0) = COUNT(1)，并且和COUNT(*)统计的行数相同。虽然概念不同，但是结果一样。
那么根据MySQL的文档：
COUNT(*) is somewhat different in that it returns a count of the number of rows retrieved, whether or not they contain NULL values.
InnoDB does not keep an internal count of rows in a table because concurrent transactions might “see” different numbers of rows at the same time. Consequently, SELECT COUNT(*) statements only count rows visible to the current transaction.
InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. There is no performance difference.
For MyISAM tables, COUNT(*) is optimized to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause.
> SELECT COUNT(*) FROM student;
This optimization only applies to MyISAM tables, because an exact row count is stored for this storage engine and can be accessed very quickly. COUNT(1) is only subject to the same optimization if the first column is defined as NOT NULL.
由于原文比较多，这里节选了一部分。可以看出来COUNT(*)返回所有查询到的行，无论是否包含NULL值（这里我测试了下，即使表只有1列，并且为NULL，也可以准确统计行数）。并且InnoDB由于并发事务无法准确的单独保存行数来提高COUNT(*)的性能。所以只会统计当前事务可见的行数。
InnoDB以相同的方式处理SELECT COUNT(*)和SELECT COUNT(1)，没有性能区别。对于MyISAM表格，如果SELECT查一个表的行数，没有同时查询其他的列，没有WHERE子句，那么COUNT(*)被优化很快返回，这是由于MyISAM的表单独保存了行数。
因此对于一般的InnoDB来说，除了COUNT(column_name)会统计NULL值外，其他都差不多。