Jump to content


Photo

*solved* when to use NULL and when not to


  • Please log in to reply
3 replies to this topic

#1 bcamp1973

bcamp1973
  • Members
  • PipPipPip
  • Advanced Member
  • 133 posts

Posted 01 September 2006 - 04:28 PM

Ok, when creating fields in MySQL (or any SQL) i've never been clear on when it's best to set the field to NULL?  I know doing so saves space (right?) but what are the disadvantages to setting a field to null?  or are there any?  I've been searching the manual and other resources, but not finding a definitive answer :(

#2 wildteen88

wildteen88
  • Staff Alumni
  • Advanced Member
  • 10,482 posts
  • LocationUK, Bournemouth

Posted 01 September 2006 - 04:38 PM

Well it depends on what the type of data the fields will hold. Say you are doing a questbook. You ahve three fields Name, Email and Website.

You obvisouly want to store the posters name and email address in the database so you make those fields not null. However for the webpage field you'll make that field NULL. As not everyone may have a website.

Thats one example of use of setting a row to null.

Null means whether the field can have a null value, meaning you are able to store nothing in that field. not null doesnt allow to store nothing in the field. So basically use NULL on a field when a field is not required to have a value when inserting data in to the database.

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 01 September 2006 - 04:51 PM

From a performance standpoint, yes, NULL requires an extra bit, affects JOINs, has implications for indexes, etc., so there's no need to use it when it's not necessary.  That being said, the only time you actually need it is when you need to know the difference between blank and NULL. 

For example, it's silly to have a NULLable gender column, since everyone has a gender, so blank (empty column) is just as good as male/female.  However, if your storing, say, how many children someone has, you need to be able to distinguish between "0" (no children) and no answer (unknown #).

Hope that makes sense.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 bcamp1973

bcamp1973
  • Members
  • PipPipPip
  • Advanced Member
  • 133 posts

Posted 01 September 2006 - 05:16 PM

Great feedback!  Thanks to both of you!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users