MySQL: NULL vs ""
Is it better to use default null or default "" for text fields in MySQL?
Why?
Update: I know what means each of them. I am interested what is better to use considering disk space and performance.
Update 2: Hey ppl! The question was "what is better to use" not "what each means" or "how to check them"...
Solution 1:
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. 1
Text columns are a little different from other datatypes. First, for "" the table entry holds the two byte length of the string followed by the bytes of the string and is a variant length structure. In the case of NULL, there's no need for the length information but it's included anyways as part of the column structure.
In InnoDB, NULLS take no space: They simply don't exist in the data set. The same is true for the empty string as the data offsets don't exist either. The only difference is that the NULLs will have the NULL bit set while the empty strings won't. 2
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.
As a result of the NULL and '' space differences, NULL and '' have NO SIZE IMPACT unless the column is specified to be NULLable or not. If the column is NOT NULL, only in MyISAM tables will you see any peformance difference (and then, obviously, default NULL can't be used so it's a moot question).
The real question then boils down to the application interpretation of "no value set here" columns. If the "" is a valid value meaning "the user entered nothing here" or somesuch, then default NULL is preferable as you want to distinguish between NULL and "" when a record is entered that has no data in it.
Generally though, default is really only useful for refactoring a database, when new values need to come into effect on old data. In that case, again, the choice depends upon how the application data is interpreted. For some old data, NULL is perfectly appropriate and the best fit (the column didn't exist before so it has NULL value now!). For others, "" is more appropriate (often when the queries use SELECT * and NULL causes crash problems).
In ULTRA-GENERAL TERMS (and from a philosophical standpoint) default NULL for NULLABLE columns is preferred as it gives the best semantic interpretation of "No Value Specified".
1 [http://forge.mysql.com/wiki/MySQL_Internals_MyISAM]
2 [http://forge.mysql.com/wiki/MySQL_Internals_InnoDB]
Solution 2:
Use default null. In SQL, null is very different from the empty string (""). The empty string specifically means that the value was set to be empty; null means that the value was not set, or was set to null. Different meanings, you see.
The different meanings and their different usages are why it's important to use each of them as appropriate; the amount of space potentially saved by using default null as opposed to default "" is so small that it approaches negligibility; however, the potential value of using the proper defaults as convention dictates is quite high.