MySQL: NULL vs ""

Question

Is it better to use default null or default "" for text fields in MySQL?

Why?

Update: I know what each of them means. I am wondering what is best used considering disk space and performance.

Update 2: Hey ppl! The question was “what is best to use,” and not “what everyone means,” or “how to test them” ...

Answer 1

Use the default value of null . In SQL, null very different from an empty string (""). An empty string means the value was empty; null means the value was not set or null was set. You see different meanings.

The different meanings and their different uses are why it is important to use each as needed; the amount of space potentially saved with default null , unlike default "" so small that it approaches negligence; however, the potential value of using the correct default values ​​as required by the agreement is quite high.

Answer 2

For tables, MyISAM NULL creates an extra bit for each NULLABLE column (zero bit) for each row. If the column is not NULLABLE, an extra bit of information is never needed. However, this is complemented by 8-bit bytes, so you always get 1 + mod 8 bytes for counting NULLABLE columns. one

Text columns are slightly different from other data types. Firstly, for "" the entry in the table contains the length of two bytes of the string, followed by the bytes of the string, and is the length structure of the variant. In the case of NULL, there is no need for length information, but it was always included as part of the column structure.

In InnoDB, NULLS do not take up space: they simply do not exist in the dataset. The same is true for an empty string, since data offsets also do not exist. The only difference is that NULL will be set to NULL, but empty lines will not. 2

When the data is actually laid out on disk, NULL and “Take EXACTLY THE SAME SPACE in both data types. However, when searching for a value, the NULL check is performed a little faster than the check for '', since you do not need to consider the length of the data in your calculations: you only check the zero bit.

As a result, NULL and '' spaces in space, NULL and '' have NO SIZE INFLUENCE unless the column indicates NULLable or not. If the column is NOT NULL, only in the MyISAM tables will you see any difference in form (and then, obviously, the default NULL value cannot be used, so a moot point).

The real question then comes down to the interpretation of the column application "no value set here". If "is a valid value meaning" the user did not enter anything here "or something like that, then by default NULL is preferable because you want to distinguish between NULL and" "when entering a record that has no data.

In general, the default is really only useful for database refactoring, when new values ​​should take effect for old data. In this case, again, the choice depends on how the application data is interpreted. For some old data, NULL is perfect and best suited (a column did not exist before it was now NULL!). For others, “more appropriate” (often when queries use SELECT * and NULL, they cause problems with failure).

In ULTRA-GENERAL TERMS (and from a philosophical point of view), NULL columns are preferable to NULLABLE columns by default because it gives a better semantic interpretation of "No Value Specified".

1 [ http://forge.mysql.com/wiki/MySQL_Internals_MyISAM]

2 [ http://forge.mysql.com/wiki/MySQL_Internals_InnoDB]

Answer 3

From High Performance MySQL, 3rd Edition

Avoid NULL if possible. Many tables include nullable columns, even if the application is not required to store NULL (no value), simply because it is the default. It is usually best to specify columns as NOT NULL if you do not intend to store NULL in them. Its harder for MySQL to optimize queries that refer to NULL columns because they complicate indexes, index statistics, and value comparisons. A column with a value of zero uses more storage space and requires special processing inside MySQL. When an index with a null value is indexed, it requires an extra byte to write and can even result in a fixed-size index (for example, an index on a single integer column) to convert to a variable size in MyISAM. The performance improvement from changing NULL columns to NOT NULL is usually small, so do not prioritize searching and modifying them according to the existing scheme if you do not know that they cause problems. However, if you plan to index columns, avoid, if possible, nullifying them. Of course, there are exceptions. For example, it is worth mentioning that InnoDB stores NULL with one bit, so it can be quite economical for sparsely populated data. However, this does not apply to MyISAM.

Answer 4

Many people answer the question of what is the difference between null and '' , but the OP requested that it takes up less space / faster, so here is my hit:

The answer is that it depends. If your field is char(10) , it will always take 10 bytes unless null , and therefore null will take up less space. A minute line by line, but over millions and millions of lines this can add up. I believe that even varchar(10) will store one byte ( \0 ) as an empty string, so again this could be due to huge tables.

In terms of performance in queries, null theoretically faster to test, but I have not seen the possibility of finding any noticeable difference in a well-indexed table. Keep in mind that you may have to convert null to '' on the application side if that is the desired revenue. Again, line by line, the difference is a minute, but it can potentially add up.

In general, this is micro-optimization, so it comes down to preference. My preference is to use null , because I like to know that there is no value, and not guess if it is an empty string ( '' ) or a bunch of spaces ( ' ' ). null is explicit in nature. '' no. So I go with null because I am the explicit kind of guy.

Answer 5

I found out that NULL vs "" is negligible in terms of disk space and performance.

The only true reason I can personally see the use of NULL over '' is when you have a field labeled UNIQUE , but it needs to allow several “empty” columns.

For example, the email column in my user table is only populated if someone really has an email address. Anyone without an email address gets NULL. I can still make this field unique, because NULL is not considered a value, whereas an empty string. ''

Answer 6

Use whatever makes sense. NULL means "no value available / defined", "" means "empty string".

If you do not allow blank lines, but the user does not need to enter a value, then NULL makes sense. If you need a value, but it can be empty, NOT NULL and the value "" makes sense.

And of course, if you do not need a value, but you can specify an empty value, then NULL makes sense.

Looking at the performance point, an extra bit is used to determine if the field is NULL or not, but don't worry about such micro-optimization until you have millions of lines.

Answer 7

I prefer null when it is semantically correct. If there is an address field, and the user did not fill it out, I give him a "". However, if in the address attribute in the user table I have not yet suggested the user to fill it out, I give him NULL.

I doubt (but cannot verify) that NULL and "" are significant.

Answer 8

"" is like an empty box ... null not like a field.

This is a complex concept that needs to be understood first, but as it is clearly stated here, there is a big difference.

Answer 9

In general, NULL should indicate data that is missing or was not provided, and therefore is a better default than an empty string.

Sometimes an empty string is what you need as a data value, but it will almost never be the default value.

Answer 10

NULL means "no value", and RDBMS is especially considered in relation to sentences and associations.

"means" empty string "and is not processed especially.

It depends on what the text represents and how it will be used in queries.

For example, you may have a questionnaire with some required questions and some optional questions.

• Rejected optional questions must be NULL in the corresponding column.
• Required questions must have an empty default string because they need to be answered. (Of course, in a real application, you would tell the user to enter something, but I hope you understand this idea).

Answer 11

'' = '' gives TRUE that satisfies the WHERE

NULL = NULL gives a NULL that does not satisfy the WHERE

What is better to use depends on what result you want to get.

If your default values ​​are NULL , there are no such requests:

 SELECT * FROM mytable WHERE col1 = ? 

will ever return these values, even if you pass NULL for the associated parameter, and this query:

 SELECT * FROM mytable WHERE col1 = '' 

will return you the lines that you set to an empty string.

This is true for MySQL , but not for Oracle , which does not distinguish between empty string and NULL .

In Oracle last query will never return anything.

Answer 12

Use "". This requires less programming effort if you can argue that the columns are not equal to zero. The difference in space between them is trivial.