Optimizing Schema and Data Types - High Performance MySQL - page 120

It's usually worth using VARCHAR when the maximum column length is much larger
than the average length; when updates to the field are rare, so fragmentation is not
a problem; and when you're using a complex character set such as UTF-8, where
each character uses a variable number of bytes of storage.
In version 5.0 and newer, MySQL preserves trailing spaces when you store and
retrieve values. In versions 4.1 and older, MySQL strips trailing spaces.
It's trickier with InnoDB, which can store long VARCHAR values as BLOB s. We discuss
this later.

CHAR

CHAR is fixed-length: MySQL always allocates enough space for the specified num-
ber of characters. When storing a CHAR value, MySQL removes any trailing spaces.
(This was also true of VARCHAR in MySQL 4.1 and older versions— CHAR and VAR
CHAR were logically identical and differed only in storage format.) Values are padded
with spaces as needed for comparisons.
CHAR is useful if you want to store very short strings, or if all the values are nearly
the same length. For example, CHAR is a good choice for MD5 values for user pass-
words, which are always the same length. CHAR is also better than VARCHAR for data
that's changed frequently, because a fixed-length row is not prone to fragmenta-
tion. For very short columns, CHAR is also more efficient than VARCHAR ; a CHAR(1)
designed to hold only Y and N values will use only one byte in a single-byte character
set, 1 but a VARCHAR(1) would use two bytes because of the length byte.
This behavior can be a little confusing, so we'll illustrate with an example. First, we
create a table with a single CHAR(10) column and store some values in it:
mysql> CREATE TABLE char_test( char_col CHAR(10));
mysql> INSERT INTO char_test(char_col) VALUES
-> ('string1'), (' string2'), ('string3 ');

When we retrieve the values, the trailing spaces have been stripped away:
mysql> SELECT CONCAT("'", char_col, "'") FROM char_test;
+----------------------------+
| CONCAT("'", char_col, "'") |
+----------------------------+
| 'string1' |
| ' string2' |
| 'string3' |
+----------------------------+

If we store the same values into a VARCHAR(10) column, we get the following result upon
retrieval:

1. Remember that the length is specified in characters, not bytes. A multibyte character set can require more
than one byte to store each character.