MySQL - CHAR vs VARCHAR - What is the difference?
CHAR
is fixed length while VARCHAR
is variable length. That means, a CHAR(x) string has exactly x characters in length, including spaces.
A VARCHAR(x) string can have up to x characters and it cuts off trailing spaces, thus might be shorter than the declared length.
In terms of efficiency, if you are storing strings with a wildly variable length then use a VARCHAR, if the length is always the same, then use a CHAR as it is slightly faster.
They also differ in maximum length where the length of a CHAR value can be any value from 0 to 255 and the maximum length of a VARCHAR value is 65,535.
An example to demonstrate the difference between CHAR and VARCHAR
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 |
Need a good GUI Tool for MySQL? TablePlus is a modern, native tool with an elegant UI that allows you to simultaneously manage multiple databases such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server and more.