"NOT NULL DEFAULT"的意义何在? [英] What's the point of "NOT NULL DEFAULT '' "?

问题描述

我已经在一个我从事的项目的数据库的许多字段上看到了这一点，在该字段中定义的列不为null，但默认值为空字符串.这样做有什么意义?如果您允许一个空字符串，为什么不只允许一个字段为空呢?

I've seen this on a lot of fields on a DB from a project I've been working on, where a column will be defined not null, but will have an empty string as the default. what's the point of doing this? If you will allow an empty string, why not just allow a field to be null?

推荐答案

NULL具有特殊的行为:将任何内容与NULL进行比较都会返回NULL，这是false或0以外的东西.意思是未知".

NULLs have special behavior: comparing anything with a NULL gives you back a NULL, which is something else than false or 0. It means "unknown".

例如，使用此表:

 user_id | gender
------------------
 1       | NULL
 2       | 'M'
 3       | 'F'
 4       | 'F'

SELECT * FROM mytable WHERE gender = 'M'将按预期返回1行

SELECT * FROM mytable WHERE gender != 'M'将返回2行，而不是3行.

SELECT * FROM mytable WHERE gender != 'M' will return 2 rows, NOT 3 rows.

SELECT * FROM mytable WHERE gender != 'M' OR gender IS NULL将返回预期的3行.

对于某些应用程序，甚至不建议使用0(或者，上帝禁止，另一个幻数")代替NULL(在此示例中，单位或精确值无关):

For some applications, using 0 (or, God forbid, another "magic number") instead of NULL is not even advisable (units or exact values are not relevant in this example):

 Date       | Temperature 
--------------------------
 2010-01-01 | 10          
 2010-01-02 | 4
 2010-01-03 | 0
 2010-01-04 | -22
 2010-01-05 | -45
 2010-01-06 | NULL
 2010-01-07 | -34

在这里，1月6日的NULL表示值未知"-可能是因为温度太低以致温度计探针停止响应.但是，这与1月3日(温度为0，即0度)的含义完全不同.

Here, the NULL on Jan 6th means "value unknown" - maybe because the temperature was so low that the thermometer probe stopped responding. However, it's a completely different meaning than Jan 3rd, when the temperature was 0, that is, 0 degrees.

此外，就像@Bill Karwin提到的那样，NULL在聚合函数(COUNT，SUM，AVG等)中表现得特别明显:对上述数据计算AVG(Temperature)会得到-14.5，因为NULL行将被忽略.

Also, as @Bill Karwin mentions, NULLs behave specially in aggregate functions (COUNT,SUM,AVG etc.): calculating AVG(Temperature) on the above data would give you -14.5, as the NULL row is ignored.

这篇关于"NOT NULL DEFAULT"的意义何在?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！