Storing empty strings vs NULLs in MySQL database?

Question

I have a PHP/MySQL application that stores "blank" values in some cases as '' (empty strings) and in other cases as NULLs.

Having this mixed format certainly causes a problem when comparing, so I’m wondering which one is the better storage mechanism? '' or NULL?

AFAIK, comparison to NULL is faster than comparison to an empty string. — Eimantas
– Eimantas, Commented Aug 4, 2011 at 10:02
Thanks @Damir for that reference - cant imagine doing that! Very scary! — siliconpi
– siliconpi, Commented Aug 15, 2011 at 7:41

Simone Gianni · Accepted Answer · 2011-08-04 10:02:33Z

8

While this is confusing, actually you should store null for a number of reasons :

Checking against null is usually faster than checking an empty string in most databases
Null commonly means "i don't know", empty string means "I know : it's empty". It gives you better semantics.

answered Aug 4, 2011 at 10:02

Simone Gianni

11.7k1 gold badge43 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

siliconpi Over a year ago

When writing to the database, if there is no content for a field, shouldnt I be saving it as NULL?

Karl Over a year ago

Yes, no content is NULL if it indicates unknown or unknowable value. For instance if you entered my 'full' name into a DB the only value you could use for my middle name is probably NULL. I know whether the string that descibes my middle name is '' or 'Laurence'. Think of an empty string a like 0, a defined value where that value is nothing and NULL as 'don't know' or 'can't know'

Simone Gianni Over a year ago

Yes, but if for example you take data from an HTML form, for fields the user has not filled, an empty string is usually sent. If you don't check for it, and "compose" the update using parameters from the HTML form directly, you end up inserting empty strings instead of nulls.

siliconpi Over a year ago

So what's the better approach? Storing NULLs or blank strings? @Karl as well

Karl Over a year ago

I recommend storing NULLS unless you know that the real value is ""; meaning we know there is no value here. This can mean having to deal with empty string to NULL conversions, e.g. @SimoneGianni's HTML form example. I also beleive that for performance and logical reason it is better to utilise NULLs.

Collectives™ on Stack Overflow

Storing empty strings vs NULLs in MySQL database?

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related