MySQL: NULL vs “”

Question

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"...

Answer 1

'' = '' yields TRUE which satisfies WHERE condition

NULL = NULL yields NULL which doesn't satisfy WHERE condition

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

If your values default to NULL, no query like this:

SELECT  *
FROM    mytable
WHERE   col1 = ?

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

SELECT  *
FROM    mytable
WHERE   col1 = ''

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

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

In Oracle, the latter query will never return anything.

Answer 2

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

The only true reason I can personally see in using NULL over '' is when you have a field marked as UNIQUE but need the ability to allow multiple "empty" columns.

For example, the email column in my user table is only filled in if someone actually has an email address. Anyone without an email address gets NULL. I can still make this field unique because NULL isn't counted as a value, whereas the empty string '' is.

Answer 3

"" is like an empty box... null is like no box at all.

It's a difficult concept to grasp initially, but as the answers here plainly state - there is a big difference.

Answer 4

In general, NULL should indicate data that is not present or has not been supplied, and therefore is a better default value than the empty string.

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

Answer 5

A lot of folks are answering the what is the difference between null and '', but the OP has requested what takes up less space/is faster, so here's my stab at it:

The answer is that it depends. If your field is a char(10), it will always take 10 bytes if not set to null, and therefore, null will take up less space. Minute on a row-by-row basis, but over millions and millions of rows, this could add up. I believe even a varchar(10) will store one byte (\0) as an empty string, so again this could add up over huge tables.

In terms of performance in queries, null is in theory quicker to test, but I haven't seen able to come up with any appreciable difference on a well indexed table. Keep in mind though, that you may have to convert null to '' on the application side if this is the desired return. Again, row-by-row, the difference is minute, but it could potentially add up.

All in all it's a micro-optimization, so it boils down to preference. My preference is to use null because I like to know that there's no value there, and not guess if it's a blank string ('') or a bunch of spaces (' '). null is explicit in its nature. '' is not. Therefore, I go with null because I'm an explicit kind of guy.

Answer 6

NULL means 'there is no value' and is treated especially by RDBMSs regarding where clauses and joins.

"" means 'empty string' and is not treated especially.

It depends on what does the text represent and how will it actually be used in queries.

For example, you can have a questionnaire with some obligatory questions and some optional questions.

• Declined optional questions should have a NULL in their corresponding column.
• Obligatory questions should have an empty string as default, because they HAVE to be answered. (Of course in a real application you'd tell the user to enter something, but I hope you get the idea)