What are database NULL fields for?

纠结着生日字段要不要允许NULL值,搜索了一下Stack Overflow刚好有问题讨论到,然后决定还是允许NULL了,不然就要使用一个特殊的值来表示,感觉并不好。 以下是转自Stack Overflow的问题,数据库中为什么使用NULL值?

It is useful to mark “unknown” or missing values.

For instance, if you’re storing people, and one of the fields is their birthday, you could allow it to be NULL to signify “We don’t know when he was born”.

This is in contrast to having non-nullable fields where, for the same type of meaning, you would need to designate a “magic value” which has the same meaning.

For instance, what date should you store in that birthday field to mean “we don’t know when he was born”? Should 1970-01-01 be that magic value? What if we suddenly need to store a person that was born on that day?

Now, having said that, NULL is one of the harder issues to handle in database design and engines, since it is a propagating property of a value.

For instance, what is the result of the following:

SELECT *
FROM people
WHERE birthday > #2000-01-01#

SELECT *
FROM people
WHERE NOT (birthday > #2000-01-01#)

(note, the above date syntax might not be legal in any database engine, don’t get hung up on it)

If you have lots of people with an “unknown” birthday, ie. NULL, they won’t appear in either of the results.

Since in the above two queries, the first one says “all people with criteria X”, and the second is “all people without criteria X”, you would think that together, the results of those two queries would produce all the people in the database.

However, the queries actually say “all people with a known and definite criteria X.”

This is similar to asking someone “am I older than 40?” upon which the other person says “I don’t know”. If you then proceed to ask “am I younger than 41?”, the answer will be the same, he still doesn’t know.

Also, any mathematics or comparisons will produce NULL as well.

For instance, what is:

SELECT 10 + NULL AS X

the result is NULL because “10 + unknown” is still unknown.

维基百科相关说明

Null or NULL is a special marker used in Structured Query Language to indicate that a data value does not exist in the database. Introduced by the creator of the relational database model, E. F. Codd, SQL Null serves to fulfil the requirement that all true relational database management systems (RDMS) support a representation of “missing information and inapplicable information”. Codd also introduced the use of the lowercase Greek omega (ω) symbol to represent Null in database theory. In SQL, NULL is a reserved word used to identify this marker.

A null should not be confused with a value of 0. A null value indicates a lack of a value, which is not the same thing as a value of zero. For example, consider the question “How many books does Adam own?” The answer may be “zero” (we know that he owns none) or “null” (we do not know how many he owns). In a database table, the column reporting this answer would start out with no value (marked by Null), and it would not be updated with the value “zero” until we have ascertained that Adam owns no books.

SQL null is a state, not a value. This usage is quite different from most programming languages, where null value of a reference means it is not pointing to any object.

Comments are closed