VARCHAR types are similar, but differ in the way they are stored and retrieved.
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.
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.
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
VARCHAR are stored as follows:
In contrast to
VARCHARvalues 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.
In conformance with standard SQL, trailing spaces are retained when values are retrieved.
Let me illustrate the difference between
VARCHAR in the following table.
In this table, I assume that the columns are usring a single-byte character, such as
Read the full documentation here. 🤓