MySQL data type char or varchar is best?

13 replies
MySQL data type char or varchar is best?

what is the difference between char and varchar?
#char #data #mysql #type #varchar
  • {{ DiscussionBoard.errors[8817728].message }}
  • Profile picture of the author brutecky
    No one can say which is best considering you didnt indicate what kind of data you want to store.

    Char has a max of 256 characters
    Var Char has a max of 65,535 characters

    So one consideration is the length of the data you wish to ad to the field.
    {{ DiscussionBoard.errors[8818160].message }}
  • Profile picture of the author carltonman
    If you specifiy CHAR as 8 characters long, than no matter if you write "one" or "eightcha", both will take up 8 characters. "one" will be written as "one_____" with the trailing space added.

    If you specify VARCHAR 8 characters long and you only want to store the word "one" in it, it will only store those three characters. No trailing space added.

    As it is explained on the link already posted:
    MySQL :: MySQL 5.1 Reference Manual :: 11.4.1 The CHAR and VARCHAR Types
    {{ DiscussionBoard.errors[8819740].message }}
  • Profile picture of the author shahriyar
    Originally Posted by rogerlegend View Post

    MySQL data type char or varchar is best?

    what is the difference between char and varchar?
    The data range is given above, the point is what type of data you want to store in it. If it is not more than 256 char, use char.
    {{ DiscussionBoard.errors[8821459].message }}
  • {{ DiscussionBoard.errors[8833083].message }}
    • Profile picture of the author Jeremy Morgan
      I generally tend to lean on varchar for just about everything. Unless it's something like state abbreviations (Oregon - OR) that you know will never change, it's always a good idea.

      As others have said it depends on what you're storing, and how much of it you're storing.
      Signature
      Jeremy Morgan, Software Developer / SEO
      Check out my Programming Blog for news, tips, and tutorials
      {{ DiscussionBoard.errors[8833880].message }}
  • Profile picture of the author raypal
    CHAR should be used for storing fix length character strings.
    VARCHAR provides for variable-length storage of strings.
    {{ DiscussionBoard.errors[8845126].message }}
  • Profile picture of the author designitbuddy
    For fixed and short length i myself use char i think if its not more than 256 than use char.
    {{ DiscussionBoard.errors[8856221].message }}
  • Profile picture of the author Jamie Lin
    Generally, varchar for everything but when you're dealing with database optimization char is a great way to go. So it really depends on what you are trying to accomplish.
    {{ DiscussionBoard.errors[8940003].message }}
  • Profile picture of the author Iconiplex
    Use CHAR when you know your data storage size requirement for any given data string will be less to its equivalent in VARCHAR and when you know your data won't exceed the maximum CHAR limit.

    Else, use VARCHAR.
    Signature
    US-based design, development, marketing, branding, printing, and all other business services.
    High-risk payment processing for pharma, e-cigs, seeds, and more!
    Accepting investment management clients. Minimum deposit is $3000.
    Skype/AIM: artizhay
    {{ DiscussionBoard.errors[8941638].message }}
  • Profile picture of the author r0dvan
    Learn to identify your data limits. As same as you choose DATE, choose CHAR and VARCHAR, everything can result in a unknown bug in future development if you do not choose carefully.
    Signature
    LeadGen.tools - Lead Generation Search Engine from any Network and many useful tools. FREE trial.
    {{ DiscussionBoard.errors[8942217].message }}
  • Profile picture of the author MonopolyOnline
    The following will best display the differences:

    ValueCHAR(4)Storage RequiredVARCHAR(4)Storage Required''' '4 bytes''1 byte'ab''ab '4 bytes'ab'3 bytes'abcd''abcd'4 bytes'abcd'5 bytes'abcdefgh''abcd'4 bytes'abcd'5 bytes

    Just stay with varchar and set the length to the max size you will need. In 19 years I have rarely used char. My cousin does massive multi gigabyte government dbs and he uses char.

    Again set the length as example:

    email varchar(50)
    first_name varchar(10)
    last_name varchar(15)
    state_abbreviated varchar(2)
    ssn varchar(10)
    postal_code varchar(10)
    username varchar(10)

    By setting the length you prevent the database from becoming bloated with unused space, plus you will help speed up searches.

    Also index each column you will search on.

    For example, email, first and last names, state, zip_codes, etc.

    Indexing will dramatically help speed up search results.

    Hope this helps.
    {{ DiscussionBoard.errors[8987385].message }}
  • Profile picture of the author Ramy31
    mysql - Any benefit of uses CHAR over VARCHAR? - Stack Overflow

    VARCHAR
    varchar stores variable-length character string. it can require less storage than fixed-length types because it uses only as much space as it needs.

    varchar also uses 1 or 2 extra bytes to record the value's length. for example varchar(10) will use up to 11 bytes of storage space. varchar helps performance because it saves space. however because the rows are variable length, they can grow when you update them, which can cause extra work. if a row grows and no longer fits in its original location, the behavior is storage engine-dependent...

    CHAR
    char is fixed-length , mysql always allocates enough space for the specified number of characters. When storing a CHAR value, MySQL removes any trailing spaces. Values are padded with spaces as needed for comparisons.

    char is useful if you want to store very short strings, or if all the values are nearly the same length. For example, CHAR is a good choice for MD5 values for user passwords, which are always the same length.

    char is also better than VARCHAR for data that’s changed frequently, because a fixed-length row is not prone to fragmentation.
    {{ DiscussionBoard.errors[8988975].message }}

Trending Topics