Difference between CHAR and VARCHAR in mysql

Both allow to store lists of characters (bytes). But the way they are stored is different.

With CHAR, length defines the exact size of the list that will be stored. With varchar, one or two bytes are used as prefix to define the amount of characters. Only one if size is lower or equal than 255, two between 256 and 65,535.

VARCHAR can be up to 65,535 size when CHAR can only be up to 255.

The following table is from MySQL documentation page

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

There is also a slightly difference in the way to use these types. CHAR goes faster because it uses static memory allocation. VARCHAR uses dynamic memory allocation and so is a bit slower. Instead, VARCHAR needs less storage when content size varies.

Considering the LENGTH choices for VARCHAR, remember that you store LENGTH + prefix = LENGTH + (1 or 2). Memory allocation may differ according to devices, operating systems, softwares, … But it always allocates powers of two. Si if you have a VARCHAR(128), you will have to store 129 bytes, and it will probably require 128 + 8/16/32 bytes or more… It also seems better to use values like 31, 63, 127, 255, 510,1022, 2046, etc

Spaces at the end of CHAR are systematically removed.

Leave a Reply

Your email address will not be published. Required fields are marked *