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.
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.
Storing of data is quite different in both types. First takes the case of the 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 “.
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.
Data retrival is also quite different for the both types.
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 will store only data which you pass on the query so like CHAR, it will not perform any space removal process on the data.
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.
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’.
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
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.
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: Avinash