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.

In this table, I assume that the columns are usring a single-byte character, such as latin1

Read the full documentation here. 🤓