MySQL NULL or NOT NULL That is The Question?

What is the difference between NULL an开发者_JAVA百科d NOT NULL? And when should they be used?

NULL means you do not have to provide a value for the field...

NOT NULL means you must provide a value for the fields.

For example, if you are building a table of registered users for a system, you might want to make sure the user-id is always populated with a value (i.e. NOT NULL), but the optional spouses name field, can be left empty (NULL)

I would suggest

• Use NOT NULL on every field if you can
• Use NULL if there is a sensible reason it can be null

Having fields which don't have a meaningful meaning for NULL nullable is likely to introduce bugs, when nulls enter them by accident. Using NOT NULL prevents this.

The commonest reason for NULL fields is that you have a foreign key field which is options, i.e. not always linked, for a "zero or one" relationship.

If you find you have a table with lots of columns many of which can be NULL, that starts sounding like an antipattern, consider whether vertical partitioning makes more sense in your application context :)

There is another useful use for NULL - making all the columns in an index NULL will stop an index record being created for that row, which optimises indexes; you may want to index only a very small subset of rows (e.g. for an "active" flag set on only 1% or something) - making an index which starts with a column which is usually NULL saves space and optimises that index.

What is the difference between NULL and NOT NULL?

When creating a table or adding a column to a table, you need to specify the column value optionality using either NULL or NOT NULL. NOT NULL means that the column can not have a NULL value for any record; NULL means NULL is an allowable value (even when the column has a foreign key constraint). Because NULL isn't a value, you can see why some call it optionality - because database table requires that in order to have a column, there must be an instance of the column for every record within the table.

And when should they be used?

That is determined by your business rules.
Generally you want as many columns as possible to be NOT NULL because you want to be sure data is always there.

NOT NULL means that a column cannot have the NULL value in it - instead, if nothing is specified when inserting a row for that column, it will use whatever default is specified (or if no default is specified, whatever MySQL's default is for that type).

Fields that aren't NOT NULL can potentially have their value as NULL (which essentially means a missing/unknown/unspecified value). NULL behaves differently than normal values, see here for more info.

As others have answered, NOT NULL simply means that NULL is not a permitted value. However, you will always have the option of empty string '' (for varchar) or 0 (for int), etc.

One nice feature when using NOT NULL is that you can get an error or warning should you forget set the column's value during INSERT. (assuming the NOT NULL column has no DEFAULT)

The main hiccup with allowing NULL columns is that they will never be found with the <> (not equal) operator. For example, with the following categorys

Desktops
Mobiles
NULL -- probably embedded devices

The = operator works as expected

select * from myTable where category="Desktops";

However, the <> operator will exclude any NULL entries.

select * from myTable where category<>"Mobiles";
-- returns only desktops, embedded devices were not returned

This can be the cause of subtle bugs, especially if the column has no NULL data during testing initial, but later some NULL values are added due to subsequent development. For this reason I set all the columns to NOT NULL.

However, it can be helpful to allow NULL values when using a UNIQUE KEY/INDEX. Normally a unique key requires the column (or combination of columns) to be unique for the whole table. Unique keys are a great safeguard that the database will enforce for you.

In some cases, you may want the safeguard for most of the rows, but there are exceptions.

If any column referenced by that particular UNIQUE KEY is NULL, then the uniqueness will no longer be enforced for that row. Obviously this would only work if you permit NULLs on that column, understanding the hiccup I explained above.

If you decide to allow NULL values, consider writing your <> statements with an additional condition to detect NULLs.

select * from myTable where category<>"Desktops" or category is null;

NOT NULL is a column constraint and should be used when you have a column that's not in a primary key (primary keys are intrinsically not-null so it's silly to say NOT NULL explicitly about them) of which you know the values will always be known (not unknown or missing) so there's no need for nulls in that column.

NULL is a keyword occurring in many contexts -- including as a column constraint, where it means the same as the default (i.e., nulls are allowed) -- but also in many other contexts, e.g. to insert a null in a column as part of an INSERT...VALUES statement.

Also note that NULL is not equal to anything else, even not to NULL itself.

For example:

mysql> select if(NULL=NULL, "null=null", "null!=null");
+------------------------------------------+
| if(NULL=NULL, "null=null", "null!=null") |
+------------------------------------------+
| null!=null                               |
+------------------------------------------+
1 row in set (0.00 sec)

This definition of NULL is very useful when you need a unique key on a column that is partially filled. In such case you can just leave all the empty values as NULL, and it will not cause any violation of the uniqueness key, since NULL != NULL.

Here is an example of how you can see if something is NULL:

mysql> select if(null is null, "null is null", "null is not null");
+------------------------------------------------------+
| if(null is null, "null is null", "null is not null") |
+------------------------------------------------------+
| null is null                                         |
+------------------------------------------------------+
1 row in set (0.01 sec)

If you're not sure, use NOT NULL.

Despite the common belief, NOT NULL doesn't require you to fill all fields; it just means whatever you omit will have the default value. So no, it doesn't mean pain. Also, NULL is less efficient in terms of indexing, and causes many edge case situations when processing what you receive from a query.



So, while of course NULL values have a theoretical meaning (and in rare cases you can benefit from this), most of the time NOT NULL is the way to go. NOT NULL makes your fields work like any variable: they always have a value, and you decide if that value means something or not. And yes, if you need all the possible values and one extra value that tells you there's simply nothing there, you can still use NULL.

So why do they love NULL so much?

Because it's descriptive. It has a semantic meaning, like "Nnah, wait, this is not just an empty string, this is a lot more exotic - it's the lack of information!" They will explain how it's different to say "time is 00:00" and "i don't knot what time it is". And this is valid; it just takes some extra effort to handle this. Because the system will allocate extra space for the information "is there a value at all" and it will constantly struggle checking it out. So for the tiny piece of semantic beauty, you sacrifice time and storage. Not much, but still. (Instead, you could have said "99:99" which is clearly an invalid time and you can assign a constant to it. No, don't even start, it's just an example.)

The whole phenomenon reminds me of the good old isset debate where people are somehow obsessed with the beauty of looking at a nonexistent array index and getting an error message. This is completely pointless. Practical defaults are a blessing, they simplify the work in a "you know what I mean" style and you can write more readable, more concise, more expressive code that will make sense even after you spend 4 years with other projects.

Inevitable NULLs

If you have JOINs, you will encounter NULLs sooner or later, when you want to join another row but it's not there. This is the only valid case where you can't really live without NULLs; you must know it's not just an empty record you got, it's no record at all.

Otherwise? NOT NULL is convenient, efficient, and gives you fewer surprises. In return, it will be called ignorant and outrageous by some people with semantical-compulsive disorder. (Which is not a thing but should be.)

TL;DR

Prefer NOT NULL when possible.
It's a weird thing - for the machine, too.