Is it a good practice to set the default value of some field/column as NULL in MySQL?

Question

What are the disadvantages to do so?

Answer 1

Is it a good practice to set the default value of some field/column as NULL in MySQL?

I think yes. Because NULL takes minimum memory irrespective of Datatype

Answer 2

There are no disadvantages to setting it as default, as that just matters once you don't set a value to it.

If the value becomes NULL, it may have implications on lookup. This is because MySQL looks through files, and skipping fields with a known length is easy, as you just jump across that length. But if a column may contain NULL, then that needs to be checked for before the jump is made. Doesn't take too much time though, so I don't think it matters all that much.

The advantage is obviously space saving, but storage is cheap. It's 2009 after all.

The fastest way to lookup stuff is to set all columns NOT NULL, and rather use stuff like empty strings and -1 (or even 0, if that doesn't mean something else) for storing 'null' values. It doesn't take THAT much more space, and it's always faster, because the length of the data is guaranteed.

Though, if your database is relatively small and doesn't need to filter 800 million rows in 2 milliseconds, then feel free to use NULL values. ;)

Answer 3

The disadvantage of allowing a column to be null impacts business logic - null means effectively there is no value associated, but there could be. If that's not correct for your business rules, then the column shouldn't allow for null values to exist within it.

NULL values are known in some cases to be expensive to query execution, and some databases won't evaluate two columns with values of null being equivalent.

BTW: Although you could set a DEFAULT constraint to make the column value to be null, that's more work than is necessary. There are three components to a column definition when creating or altering a table:

1. The column name
2. The data type for the column
3. The optionality - if the column will allow null values to exist

Answer 4

Depending on your DBMS, NULL will be the default default. That is, a NULL will be stored whenever you don't provide a value, and no default has been set up (and, of course NULL is not forbidden).

The real cost of NULLS comes when the values are to be interpreted. SQL uses uses a strange and sometimes counterintuitive three valued logic in evaluating expressions where NULLs might be involved. The third value is UNKNOWN, and the counterintuitive part is that NOT UNKNOWN equals UNKNOWN. If you aren't careful, you can get wrong results.

A statistic like SUM(COLUMN) can be understated if some of the entries have NULL in the COLUMN, and those entries have a value in the real world.

But the real kick in the head comes from the tendency of database designers and programmers to use NULL to convey some kind of meaningful information, instead of just "NO DATA HERE". This almost always causes logical errors. If you have data, store data. If you have no data, and no choice about it, store a NULL. And don't let yourself be misled by the absence of data.

Answer 5

Merely omitting NOT NULL from column definitions sometimes has significant performance impact. So I've grown accustomed to including it wherever I possibly can.