6.2.3.1 The CHAR and VARCHAR Types

The CHAR and VARCHAR types are similar, but differ in the way they are stored and retrieved.

The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value between 1 and 255. (As of MySQL Version 3.23, the length of CHAR may be 0 to 255.) When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed.

Values in VARCHAR columns are variable-length strings. You can declare a VARCHAR column to be any length between 1 and 255, just as for CHAR columns. However, in contrast to CHAR, VARCHAR values are stored using only as many characters as are needed, plus one byte to record the length. Values are not padded; instead, trailing spaces are removed when values are stored. (This space removal differs from the SQL-99 specification.) No case conversion takes place during storage or retrieval.

If you assign a value to a CHAR or VARCHAR column that exceeds the column's maximum length, the value is truncated to fit.

The following table illustrates the differences between the two types of columns by showing the result of storing various string values into CHAR(4) and VARCHAR(4) columns:

The values retrieved from the CHAR(4) and VARCHAR(4) columns will be the same in each case, because trailing spaces are removed from CHAR columns upon retrieval.

Values in CHAR and VARCHAR columns are sorted and compared in case-insensitive fashion, unless the BINARY attribute was specified when the table was created. The BINARY attribute means that column values are sorted and compared in case-sensitive fashion according to the ASCII order of the machine where the MySQL server is running. BINARY doesn't affect how the column is stored or retrieved.

From version 4.1.0, column type CHAR BYTE is an alias for CHAR BINARY. This is a compatibility feature.

The BINARY attribute is sticky. This means that if a column marked BINARY is used in an expression, the whole expression is compared as a BINARY value.

MySQL may silently change the type of a CHAR or VARCHAR column at table creation time. See section 6.5.3.1 Silent Column Specification Changes.

User Comments

At times it can get annoying that MySQL cuts of trailing spaces with CHAR and VARCHAR columns, for example when you want to store a hash digest (such as MD5 or SHA-1) as binary data in a CHAR column. If a digest happens to have a space character at the end, it will be missing when retrieving the hash from the db. The fix is to use RPAD() when retrieving columns, for example for SHA-1 hashes: SELECT RPAD(hash, 20, ' ') AS hash FROM ...

Add your own comment.