Empty LONGTEXT or NULL?

Question

In my MySQL DB I have one field called html_contents that contains all the html to be shown in a webpage. Obviously the html could be huge, and certaintly bigger than 64KB, therefore I decided to use LONGTEXT rather than VARCHAR.

When the field is not set or left empty would you say it is better (alias more efficient for the DB) to set it to NULL or to empty string?

I read this: When to use NULL and when to use an empty string? but it talks about empty strings in general (probably small strings and not LONGTEXT).

I was wondering if with LONGTEXT is a different story, whether it saves a lot of space or execution time to use NULL instead of leaving empty LONGTEXT around.

Answer 1

A good starting point to make a decision in order to use or not this kind of value is the MySQL official manual.

In short:

Optimization

Declare columns to be NOT NULL if possible. It makes SQL operations faster, by enabling better use of indexes and eliminating overhead for testing whether each value is NULL. You also save some storage space, one bit per column. If you really need NULL values in your tables, use them. Just avoid the default setting that allows NULL values in every column.

Problems with NULL values

In SQL, the NULL value is never true in comparison to any other value, even NULL. An expression that contains NULL always produces a NULL value unless otherwise indicated in the documentation for the operators and functions involved in the expression

When reading data with LOAD DATA INFILE, empty or missing columns are updated with ''. To load a NULL value into a column, use \N in the data file

Answer 2

In general this can be somewhat application-specific. The real question is, to my mind, what is the significance of an empty value? Keep in mind that the choice changes the semantics of SQL queries processing this value so this is where to focus on.

As a general rule, I prefer to go with NULL where the value may exist but is not known and empty string where it is assumed that we know that the value does not apply. This keeps many things sane. For example text concatenation of a null and a not null produces a null. This is helpful if we want to know that the result is actually unknown (and there are cases we do). At any rate if the value somewhere is unknown and we want to use an empty string as a placeholder, we have to do this explicitly. Similarly if the value is known not to apply, we can use concat an empty string as is.

As far as space goes, I think you will find that semantic clarity far outweighs any difference there. In any sane db implementation I would not expect there to be a huge space difference.

Answer 3

There is a class of problems for which the empty string is useful, and carries meaning that NULL specifically does not.

The empty string is the identity under the concatenate operator. If you concatenate the empty string with any string you get the same string back. If your application manipulates strings in this way, then reserving the empty string for a different purpose than NULL makes sense.

For comparison purposes, zero is the the identity under the addition operator. You wouldn't want to use the number zero to indicate the absence of a number. It would mess up your averages, just for starters.

Avoid NULL when possible. Use NULL when necessary.