4

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?

Teun Zengerink
  • 4,277
  • 5
  • 30
  • 32
siliconpi
  • 8,105
  • 18
  • 69
  • 107

1 Answers1

7

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

  1. Checking against null is usually faster than checking an empty string in most databases
  2. Null commonly means "i don't know", empty string means "I know : it's empty". It gives you better semantics.
Simone Gianni
  • 11,426
  • 40
  • 49
  • When writing to the database, if there is no content for a field, shouldnt I be saving it as NULL? – siliconpi Aug 04 '11 at 10:10
  • 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' – Karl Aug 04 '11 at 10:14
  • 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. – Simone Gianni Aug 04 '11 at 10:21
  • 1
    So what's the better approach? Storing NULLs or blank strings? @Karl as well – siliconpi Aug 15 '11 at 07:43
  • 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. – Karl Aug 18 '11 at 09:07