Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

Null vs Space



1:41 pm on Dec 27, 2009 (gmt 0)

10+ Year Member

If a text field (varchar) is not required, which one is better to use INSERT NULL or INSERT ''?

Is it true that inserting null instead of space ('') will save you some disk space?


5:39 pm on Dec 27, 2009 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

Don't insert anything at all, and let the database default value populate the field.

foreach ($input as $key=>$value) {
if (isset($value)) {
$fields .= "$key,";
$values .= "'$value',";
if ($fields and $values) {
$fields = preg_replace('/,$/','',$fields); // or other method
$values = preg_replace('/,$/','',$values); // to chop last comma
$query = "insert into table ($fields) values ($values)";
// etc., execute query
else { echo "nothing to insert"; }

As for the null/empty string, this is really a decision you make based on how you plan to work the database, not how much more or less space it takes. That is, if "optional fields" are expected to be null, use null, otherwise, set the default to "not null" and it will be a blank string, which is very different than null.

problems with null [dev.mysql.com]

How much actual space depends on whether you're using myISAM or INNoDB. There are quite a few resources to be found on this, one I found that says it better than I ever could:

For MyISAM tables, NULL creates an extra bit for each NULLABLE column (the null bit) for each row. If the column is not NULLABLE, the extra bit of information is never needed. However, that is padded out to 8 bit bytes so you always gain 1 + mod 8 bytes for the count of NULLABLE columns.

In InnoDB, NULLS take no space: They simply don't exist in the data set ...... When the data is actually laid out on disk, NULL and '' take up EXACTLY THE SAME SPACE in both data types. However, when the value is searched, checking for NULL is slightly faster then checking for '' as you don't have to consider the data length in your calculations: you only check the null bit.

By that description, you can see that it's not so much the space that's an advantage with null columns without value, it's the performance.

Another example, I've encountered some perl modules that won't run if you have any columns set to "not null."

I guess if you really want to know the difference in disk space the definitive test would be to create two tables, one with not null and one with null columns, populate them with a million records and compare the sizes of the actual table files.


Featured Threads

Hot Threads This Week

Hot Threads This Month