SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Hybrid View

  1. #1
    [Call me Bram] iBram007's Avatar
    Join Date
    Feb 2001
    Location
    Belgium
    Posts
    339
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello guys,

    I'm learning mySQL at the moment, togheter with phpmyadmin.
    When I want to create a table, I can choose from a whole list of table types:

    TINYINT,
    SMALLINT,
    MEDIUMINT,
    INT,
    BIGINT,
    FLOAT,
    DOUBLE,
    DECIMAL,
    DATE,
    DATETIME,
    TIMESTAMP,
    TIME,
    YEAR,
    CHAR,
    VARCHAR,
    TINYBLOB,
    TINYTEXT,
    TEXT,
    BLOB,
    MEDIUMBLOB,
    MEDIUMTEXT,
    LONGBLOB,
    LONGTEXT,
    ENUM,
    SET


    Can anyone me explain the differents or give me an url with the differents between this types?
    Thanks in advance!

  2. #2
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your best bet is to read through chapter 7.3 of the manual http://www.mysql.com/doc/C/o/Column_types.html

  3. #3
    SitePoint Wizard silver trophy Karl's Avatar
    Join Date
    Jul 1999
    Location
    Derbyshire, UK
    Posts
    4,411
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just a quick note, these aren't table types, they are column types, the MySQL table types are:

    ISAM
    MyISAM
    HEAP
    BDB - Berkeley Database, which is the table type used when you need to support transactions.
    Karl Austin :: Profile :: KDA Web Services Ltd.
    Business Web Hosting :: Managed Dedicated Hosting
    Call 0800 542 9764 today and ask how we can help your business grow.

  4. #4
    [Call me Bram] iBram007's Avatar
    Join Date
    Feb 2001
    Location
    Belgium
    Posts
    339
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanx guys!

    Ok, I have that list, but don't understand it

    What type do I use for:
    - a text block
    - numbers (zip codes, telefon#,...)
    - address line (street)
    - url

  5. #5
    SitePoint Guru
    Join Date
    Jan 2001
    Location
    Alkmaar, Netherlands
    Posts
    710
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    For Text you have following options and I took them all from the page freakysid mentioned they look very clear to me.
    TINYTEXT
    A BLOB or TEXT column with a maximum length of 255 (2^8 - 1) characters.
    MEDIUMTEXT
    A BLOB or TEXT column with a maximum length of 16777215 (2^24 - 1) characters.
    LONGTEXT
    A BLOB or TEXT column with a maximum length of 4294967295 (2^32 - 1) characters.
    and for the rest I recommend you to check that page again to understand. First check your field values range and write it down and then go to that web page and see which data type is proper for that range.
    As an example, zip codes in Nederland includes 4 number + 2 character, in US and Turkey it is 5 digit integer. so I recommend you choose varchar instead of number type.
    For address line, I would use varchar(150) it should be enough.
    For url varchar(250) is long enough I think.
    Last edited by sylow; May 6, 2001 at 06:03.

  6. #6
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by iBram007
    Thanx guys!

    Ok, I have that list, but don't understand it

    What type do I use for:
    1 a text block
    2 numbers (zip codes, telefon#,...)
    3 address line (street)
    4 url
    These are just my suggestions:
    1) TEXT

    VARCHAR can be up to 255 characters. Anything over this requires a TEXT type. VARCHAR and TEXT types accommodate variable string length, meaning that they will only take up storage according to the length of the string (unlike a fixed length string - the CHAR type.

    At the end of the day, the only difference between the variable string length types is the number of bytes needed to store the string length:

    VARCHAR - requires 1 byte,
    TINYTEXT - requires 1 byte,
    TEXT - requires 2 bytes,
    MEDIUM TEXT - requires 3 bytes,
    LARGETEXT - requires 4 bytes.

    Note that TEXT types cannot be indexed while VARCHAR can be indexed (so, for example a TEXT type cannot be part of the primary key).

    2) Depends on the type of number

    For an integer (whole number) the decision to make is which range of values to support - because the larger the int type the more bytes will be used to store the number. From the manual:
    TINYINT[(M)] [UNSIGNED] [ZEROFILL]
    A very small integer. The signed range is -128 to 127. The unsigned range is 0 to 255.
    SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
    A small integer. The signed range is -32768 to 32767. The unsigned range is 0 to 65535.
    MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
    A medium-size integer. The signed range is -8388608 to 8388607. The unsigned range is 0 to 16777215.
    INT[(M)] [UNSIGNED] [ZEROFILL]
    A normal-size integer. The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295.
    BIGINT[(M)] [UNSIGNED] [ZEROFILL]
    A large integer. The signed range is -9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615.
    For floating point numbers - use FLOAT or DOULBE (again - depending on the range and precision choose between float or doulbe - double is double the size/precision but requires twice the storage space).

    Also, is a code a number - usually not! I would not consider a telephone number a number - but a numeric code - so would use a CHAR or VARCHAR type for it. A zip code, on the other hand, you may want to perform mathematical operations on (eg, find all the zip codes within the range of 1000 to 3000). So an SMALLINT UNSIGNED would be OK to use here (assuming that zip codes don't go over approx 65000).

    3) VARCHAR(M) - You most likely want to limit the length of the string "M" to some number - say 50 characters - so VARCHAR is suitable.

    4) URL - again VARCHAR would be the go here.

  7. #7
    [Call me Bram] iBram007's Avatar
    Join Date
    Feb 2001
    Location
    Belgium
    Posts
    339
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanx guys!


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
  •