SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Data Type Basics (Varchar vs Char, Int, etc.)

    I started out using Varchar, Int and Text data types and recently began using Char and TinyInt, but I don't really understand the differences - partly because some references give conflicting information.

    Varchar sounds pretty simple. If I have a database table field with 100 rows containing animal names, like dog and Tyrannosaurus_rex, then Varchar should be set at Varchar(?), where ? is the number of characters in the LONGEST name in the field (16 for T rex), right?

    Now, suppose I have a field with ID's for nations and states, like these:

    can
    usa
    us-az
    us-ca
    mex

    Can I use CHAR(5) for this field, because the values are so tiny, or does every value in a CHAR field have to have exactly the same number of characters?

    What's the difference betwen VARCHAR and TINYTEXT if the maximum value for both is 255 characters?

    Also, it looks like I should start using MEDIUM TEXT instead of TEXT if my values are generally less than a thousand characters, right? Will replacing TEXT with MEDIUM TEXT have a big bearing on performance?

    NUMERALS

    Each of my tables begins with a numerical key. The smallest tables only have a few dozen rows, while the biggest have about 50,000. Am I correct in understanding that you can use TinyInt only if the biggest numeral is 127? In other words, I could use TinyInt for every table with less than 128 rows?

    And I could use SMALLINT for all my other numerical keys - or for any numerical field that doesn't exceed 32,767, right? (I haven't looked up the definition of "unsigned" yet, but I assume my fields are NOT unsigned by default, right?)

    Thanks.

  2. #2
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Normalizing a Big Table + Cardinality

    (Oops - please ignore this post.)

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    everything in your first post sounds right, except for the bit about all the values in a CHAR field needing to be the same number of characters -- in fact, any values shorter than the field length are padded on the right with blanks

    except if the table contains even one (1) VARCHAR, in which case all CHAR fields longer than 4 characters are "silently" converted to VARCHAR, but this is a peculiarity unique to mysql and you shouldn't need to worry about it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •