CHAR vs VARCHAR in MySQL

  • Avinash
  • 4
  • Dec 05, 2011
  • Database

CHAR and VARCHAR are the datatypes of the MySQL.

This article is to cover similarity and difference of both datatypes. Both are almost same but for some aspects both behave slight different based on condition.

Introduction

CHAR and VARCHAR are almost same but both are different at the stage of storing and retriving the data from the database.

For both the datatypes we have to pass the length specifier, it indicates that how much data that fields can hold. For example char(30) and varchar(30) this means fields with these datatypes can hold upto 30 characters.

For CHAR this length can be any from 0 to 255 and for VARCHAR can be from 0 to 65,535. But for VARCHAR this maximum limit depends on Maximum row size and character set you have used.

Data Storage

Storing of data is quite different in both types. First takes the case of the CHAR.

CHAR

When values are stored in CHAR fields, remaining chars will be padded with the white spaces.

Say for example one fields is “name CHAR(5)” and you are about to store just “jack” then actual value stored will be “jack “.

name CHAR(5) --------------------------------------- value actual_storage --------------------------------------- 'ex' 'ex ' 'exper' 'exper' 'expert' 'exper' ---------------------------------------
  name CHAR(5)

  ---------------------------------------
    value         actual_storage
  ---------------------------------------
    'ex'          'ex     '  
    'exper'       'exper'
    'expert'      'exper'
  ---------------------------------------

VARCHAR

VARCHAR will not add extra spaces to your data when you provide the less data than specified length. It will store the data as you have provide in the query.

For both the data types, If you pass the larger data than specified length then MySQL will truncate the data to fit the length specified at the time of creating a table and it will return a warning. But this will be the case if SQL Strict Mode is not enabled.

But if you want to restrict this truncation of the data you should enable the Strict Mode in MySQL. So it will generate error for the query.

name VARCHAR(5) --------------------------------------- value actual_storage --------------------------------------- 'ex' 'ex' 'exper' 'exper' 'expert' 'exper' ---------------------------------------
  name VARCHAR(5)

  ---------------------------------------
    value         actual_storage
  ---------------------------------------
    'ex'          'ex'  
    'exper'       'exper'
    'expert'      'exper'
  ---------------------------------------

Data Retrival

Data retrival is also quite different for the both types.

CHAR

As mentioned above CHAR will add the spaces to the data if less data is provided, so while retrival of the data it will remove the added spaces and provide the data.

VARCHAR

VARCHAR will store only data which you pass on the query so like CHAR, it will not perform any space removal process on the data.

Space Occupied by Each Other

There is a bit difference in storage space required for both datatypes.

First of all, assuming that fields are using single byte character set like latin1.

CHAR

As mentioned above CHAR will add the space to the remaining word count. So CHAR will always occupy the number of bytes based on the length specified. So CHAR(5) field will always occupy 5 bytes doesn’t matter what we have stored, like ‘a’, ‘abcd’ or ‘abcde’.

name CHAR(5) --------------------------------------- value actual_storage Space --------------------------------------- 'ex' 'ex' 5 bytes 'exper' 'exper' 5 bytes 'expert' 'exper' 5 bytes ---------------------------------------
  name CHAR(5)

  ---------------------------------------
    value      actual_storage   Space
  ---------------------------------------
    'ex'       'ex'             5 bytes
    'exper'    'exper'          5 bytes
    'expert'   'exper'          5 bytes
  ---------------------------------------

VARCHAR

Unlike CHAR, VARCHAR will only occupy the space based on the data stored, So for example if we have fields with VARCHAR(30) then, this will occupy the space based on the data we are going to store.

One more thing with VARCHAR is, it also occupy the 1 or 2 bytes of length prefix. This 1 and 2 bytes are based on length of data we store. If data is less than 255 char then 1 Byte is used and for data larger than 255 char 2 bytes are occupies as the length prefix

name VARCHAR(5) --------------------------------------- value actual_storage Space --------------------------------------- 'ex' 'ex' 3 bytes 'expe' 'expe' 5 bytes 'expert' 'exper' 6 bytes ---------------------------------------
  name VARCHAR(5)

  ---------------------------------------
    value      actual_storage   Space
  ---------------------------------------
    'ex'       'ex'             3 bytes
    'expe'    'expe'            5 bytes
    'expert'   'exper'          6 bytes
  ---------------------------------------

CHAR or VARCHAR?

So now the question is, which one to use? Use of these datatypes are purely depends on the need.

If you have fixed size of data, just like the flags of “Y” and “N”. Then it will better to use CHAR rather than VARCHAR. The reason is that length prefix used with VARCHAR.

So if you use VARCHAR then it will store 2 bytes for “Y” and “N”. (i.e. 1 byte for data and 1 byte for length prefix) and CHAR will store only 1 bytes which is of data only.

Related Posts

Written by Avinash

Avinash Zala is leading various projects which deals with the various technology involved with the web. A combination of perfect technical and management skills. Avinash would like to chat with you and convert your imagination into the working system. You can get in touch with him on Facebook and Twitter.

View all posts by:

  • Fab

    I think you missed an important point when choosing between those 2 types.
    For large datasets, it can be useful to use only fixed size records, so only CHAR types.
    Why ?
    Imagine MySQL wants to retrieve the 10th record of a set.
    With VARCHAR, it has to read the prefix of the first record to know at which byte the 2nd begins, etc.
    With CHAR only, MySQL knows that every record is x bytes long, so the 10th record is at the 10*x byte of the file.

    So, when working with large fact tables, it can be a good practice to use only CHAR types.
    It can greatly reduce your I/O. The time to remove spaces is clearly negligible compared to this.

    (if you really need those varchars, you can even consider moving them to another table)

  • http://webspeaks.in Arvind Bhardwaj

    Great tutorial.
    Easy and clear.

    Indian developers rock.
    Keep it up…

  • Pingback: MySQL CHAR?VARCHAR?? | 3tgame

  • Pingback: Store Images in MySQL Database with PHP | Expert PHP Developer