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)

COUNT函数不统计NULL:

 select count(Birth_place) from Singer;
 0
 and sum(NULL) gives a NULL answer.

绝大多数操作符如果有一个操作数为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函数[编辑]

 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'

反直觉的麻烦[编辑]

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

 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'