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.

  • I'm also very interested in this topic. Sometimes I have to save 1 word in the field, another time a whole article. (localization solution). I'm thinking of making 2 seperate tables: 1 with varchars and 1 with longtexts, but if it doesn't matter when it's null, I will go with that. Maikel
    – user15988
    Dec 8, 2012 at 17:07

3 Answers 3


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:


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


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.


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.