Thursday, July 8, 2010

CHAR Vs VARCHAR In Mysql

CHAR datatype used when you know the exact length of the value, ex: US Zipcodes (CHAR(7)), Mobile phone numbers (10 numbers only CHAR(10)) etc., And its length should be 0 to 255

VARCHAR datatype used in variable length inputs. Ex; User_firstname (VARCHAR(20)), street_address(VARCHAR(100)) etc.,
Its length should be 0 to 255 before Mysql 5.0.3 version, After 5.3.0 and later versions it should be 0 to 65,535.

See the example below:

Value CHAR(4) Storage Required VARCHAR(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