MySQL/Language/Using NULL

概述

NULL是SQL的一个特殊值，同义"Unknown"。 NULL值可赋值、可比较。

 INSERT into Singer
        (F_Name, L_Name, Birth_place, Language) 
        values 
        ("", "Homer", NULL, "Greek"),
        ("", "Sting", NULL, "English"),
        ("Jonny", "Five", NULL, "Binary");

空字符串不是NULL。

与NULL比较：

 
 SELECT * from Singer WHERE Birth_place IS NULL;
 or
 SELECT * from Singer WHERE Birth_place IS NOT NULL;
 or
 SELECT * from Singer WHERE isNull(Birth_place)

如果某列存在 NULL 值时，如果在该列上执行“不等于”查询，select结果不包含为 NULL 值的行。解决办法是增加isnull(col_name)查询条件。

COUNT函数指定列名时不计数NULL值所在的行，但指定参数为*则统计NULL：

 select count(Birth_place)，count(*) from Singer;
 0, 1

阿里巴巴《Java开发手册》强制规定：不要使用 count(列名) 或 count(常量) 来替代 count()，count() 是 SQL92 定义的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。说明：count(*) 会统计值为 NULL 的行，而 count(列名) 不会统计此列为 NULL 值的行。

使用 count(distinct col1, col2) 查询时，如果其中一列为 NULL，那么即使另一列有不同的值，那么查询的结果也会将数据丢失

使用sum在包含NULL的列上计算，结果为NULL 。可使用select ifnull(sum(col_name), 0) from tbk_name

绝大多数操作符如果有一个操作数为NULL，结果也将是NULL：

 SELECT (NULL=NULL) OR (NULL<>NULL) OR (NOT NULL) OR (1<NULL) OR (1>NULL) OR (1 + NULL) OR (1 LIKE NULL)

COALESCE函数

函数COALESCE (expression_1, expression_2, ...,expression_n)从左至右依次计算各参数表达式，遇到非null值即停止计算并返回该值。如果所有的表达式都是空值，最终将返回一个空值。

例如，把空值当作数值0：

 SELECT COALESCE(colname,0) from table where COALESCE(colname,0) > 1;

把NULL的日期当作当前日期：

 ORDER BY (COALESCE(TO_DAYS(date),TO_DAYS(CURDATE()))-TO_DAYS(CURDATE()))

 EXP(SUM(LOG(COALESCE(''*the fieldName you want to multiply*'',1)))

 SELECT t4.gene_name, COALESCE(g2d.score,0), 
 COALESCE(dgp.score,0), COALESCE(pocus.score,0) 
 FROM t4 
 LEFT JOIN g2d ON t4.gene_name=g2d.gene_name 
 LEFT JOIN dgp ON t4.gene_name=dgp.gene_name 
 LEFT JOIN pocus ON t4.gene_name=pocus.gene_name;

IFNULL函数

用于判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个参数的值 (同COALESCE一样，能做到在判断第一个值为空的情况下，直接返回第二个值,不同的是ifnull只能传两个参数，而COALESCE可以传递多个参数)

 IFNULL(expr1,expr2)

如果expr1不为NULL, IFNULL()返回expr1, 否则返回expr2.

 mysql> SELECT IFNULL(1,0);
 -> 1
 mysql> SELECT IFNULL(NULL,10);
 -> 10
 mysql> SELECT IFNULL(1/0,10);
 -> 10.000
 mysql> SELECT IFNULL(1/0,'yes');
 -> 'yes'

isnull

判断是不是null,若是则返回1，若不是返回0

nullif

若第二个参数等于第一个参数则返回null，否则返回第一次参数

反直觉的麻烦

空值处理可能是反直觉的。例如下述语句将删除表中所有行：

 DELETE FROM my_table WHERE field > NULL --(or function returning NULL)

如果希望ORDER BY时让空值排在最后：

 SELECT * FROM my_table ORDER BY ISNULL(field), field [ ASC | DESC ]

确定一张表中哪些列禁止为空：

SELECT *
FROM  `information_schema`.`COLUMNS`
WHERE IS_NULLABLE = 'NO' AND TABLE_NAME = 'my_table'