SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict Volitics's Avatar
    Join Date
    Aug 2003
    Location
    US
    Posts
    280
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    When do I use NULL as Default value for SET, CHAR, VARCHAR data types?

    Hello;

    I have two questions. One is about the SET data type. The other is about the CHAR and VARCHAR data types.

    Question 1 regarding SET

    I am trying to figure out when it is proper to use NULL as one of the values.

    For example, if I need no value do I use an empty value like this:

    set('', 'Yes', 'No')

    or would it be best to set it to NULL like this:

    set('NULL', 'Yes', 'No')

    On this page in the MySQL manual it uses NOT NULL for the SET value at the top of the page. However, about 1/8 way down the page it says that SET values are sorted numerically and that NULL values sort before non-NULL SET values.
    http://dev.mysql.com/doc/refman/5.0/en/set.html


    Question 2 regarding CHAR, VARCHAR

    Same as question above. If I need just an empty value in a table cell do I use NULL in the database table or do I just use a blank table cell.

    For the INT data types: I am having to use NULL for the int types because if I use an empty value PHP will display a "0" in the browser instead of no value.


    I would appreciate any information.

    Thanks.
    .

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Volitics View Post
    I am trying to figure out when it is proper to use NULL as one of the values.
    whenever you want to allow the column to be missing its value

    NULL is not a value

    Quote Originally Posted by Volitics View Post
    or would it be best to set it to NULL like this:

    set('NULL', 'Yes', 'No')
    that's not a NULL, that's a 4-character non-NULL string

    by the way, SET is one of those proprietary mysql datatypes (ENUM is another) which i urge people not to use because of portability issues

    SET is damned convenient, though, and not using it is typically more difficult, as it usually involves two additional tables


    Quote Originally Posted by Volitics View Post
    If I need just an empty value in a table cell do I use NULL in the database table or do I just use a blank table cell.
    NULL is not the same as the zero-length or "empty" string, and it is not the same as a blank

    NULL is for when you want the column value to be undefined, unknown, not appropriate, or missing
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict Volitics's Avatar
    Join Date
    Aug 2003
    Location
    US
    Posts
    280
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937;

    Thanks for responding to my question. I appreciate the information.
    .


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •