How CHAR and VARCHAR differ in the way they are stored and retrieved
TIL CHAR
and VARCHAR
types are similar, but differ in the way they are stored and retrieved.
CHAR
Storing
- Min. length:
0
- Max. length:
255
CHAR
columns are fixed to the length you declare when you create a table. Regardless of the amount of characters you use, all CHAR
columns will require the same storage, because when CHAR
values are stored, they are right-padded with spaces to the specified length.
Retrieving
When CHAR
values are retrieved, trailing spaces are removed. However, trailing spaces are not removed if the PAD_CHAR_TO_FULL_LENGTH
SQL mode is enabled.
VARCHAR
Storing
- Min. length:
0
- Max. length:
65,535
The maximum length is actually subject to the maximum row size—65,535 bytes, which is shared among all columns—and the character set used. Also, VARCHAR
values are not padded when they are stored. Furthermore, trailing spaces retained when values are stored.
The MySQL documentation describes the contrast between the way CHAR
and VARCHAR
are stored as follows:
In contrast to
CHAR
,VARCHAR
values are stored as a 1-byte or 2-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes.
Retrieving
In conformance with standard SQL, trailing spaces are retained when values are retrieved.
Example
Let me illustrate the difference between CHAR
and VARCHAR
in the following table.
Value | CHAR(4) |
Storage Required | VARCHAR(4) |
Storage Required |
---|---|---|---|---|
'' |
' ' |
4 bytes | '' |
1 byte |
'to' |
'to ' |
4 bytes | 'to' |
3 bytes |
'tonn' |
'tonn' |
4 bytes | 'tonn' |
5 bytes |
'tonnygar' |
'tonn' |
4 bytes | 'tonn' |
5 bytes |
In this table, I assume that the columns are usring a single-byte character, such as latin1
Read the full documentation here. 🤓