SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    May 2001
    Location
    Hamburg, Germany
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    char or varchar in mysql?

    Hi,

    following the 228 tutorial by Kevin Yank i added fields to an existing mysql table by using the command

    alter table xyz add zyx char(20) not null;

    when i have the table displayed the field's type sometimes comes up as varchar(20), sometimes as char(20).

    is there a difference between these two?

    Thanks for your help,
    Frank
    That' it.

  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)
    The CHAR type is a fixed length string. So if you have CHAR(12) and you have the strings:

    foo
    foobar
    foobarzoot

    They will each take up the same amount of storage space (12 bytes) in the database because if the column type is CHAR.

    The advantage to this is that if you have a table where all of the columns are of fixed length (including CHAR types) then MySQL should be able to perform queries faster. This is because, once MySQL has located the record in the index, it can calculate the possition of that record in the table (as each record will be offset by the same amount of bytes). The downside is that to create a table using all fixed length columns, you will generally end up wasting storage space when you store strings that are in fact shorter than the size of the CHAR column they are going into. So there is a trade-off between gain in performance from having fixed length records and greater stogage requirements, plus a counter-balancing speed hit from having to move around a larger file. Well, that's all rather theoretical and probably doesn't make much difference in most cases. None the less, I would love some MySQL guru to give an algorithm or rule-of-thumb as to how to decide at which point those counter-acting forces tip either way.

    All the above is well and good, but here is the trick. If your table has any columns that are not of fixed length (VARCHAR, TEXT, BLOB) then there is no advantage to having a column of CHAR mixed in, because the records will be variable length anyway. In this case, even if you declare a column of CHAR, MySQL will ignore you and save it as VARCHAR to save on storage space. That may explain some of the behaviour you have noticed.

  3. #3
    SitePoint Member
    Join Date
    May 2001
    Location
    Hamburg, Germany
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Great, thanks!
    That' it.

  4. #4
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    if you make a VARCHAR(3) or less than 3, it will become a CHAR. and, as said, if the rows are variable length a CHAR(4) or greater than 4 will become a VARCHAR.

    explained here: www.mysql.com/doc/S/i/Silent_column_changes.html

  5. #5
    SitePoint Evangelist ucahg's Avatar
    Join Date
    Apr 2001
    Location
    Sarnia, Ontario, Canada
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    CHAR would be useful if you are storing information that you absolutely know will be a certain number of characters long.

    Zip codes/Postal Codes (5 and 6, respectedly), md5() (32), etc.


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
  •