11.4.1. The CHAR and VARCHAR Types
The CHAR and VARCHAR types
are similar, but differ in the way they are stored and
retrieved. They also differ in maximum length and in whether
trailing spaces are retained. No lettercase conversion takes
place during storage or retrieval.
The CHAR and VARCHAR types
are declared with a length that indicates the maximum number of
characters you want to store. For example,
CHAR(30) can hold up to 30 characters.
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 from 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. The length can be specified as a value from 0 to
65,535. (The maximum effective length of a
VARCHAR is determined by the maximum row size
and the character set used. The maximum length overall is 65,532
bytes.)
In contrast to CHAR,
VARCHAR values are stored using only as many
characters as are needed, plus one byte to record the length
(two bytes for columns that are declared with a length longer
than 255).
VARCHAR values are not padded when they are
stored. Trailing spaces are retained when values are stored and
retrieved, in conformance with standard SQL.
If you assign a value to a CHAR or
VARCHAR column that exceeds the column's
maximum length, the value is truncated to fit. If the truncated
characters are not spaces, a warning is generated. For
truncation of non-space characters, you can cause an error to
occur (rather than a warning) and suppress insertion of the
value by using strict SQL mode. See
Section 5.2.5, “The Server SQL Mode”.
The following table illustrates the differences between
CHAR and VARCHAR by
showing the result of storing various string values into
CHAR(4) and VARCHAR(4)
columns:
Note that the values shown as stored in the last row of the
table apply only when not using strict
mode; if MySQL is running in strict mode, values that
exceed the column length are not stored,
and an error results.
If a given value is stored into the CHAR(4)
and VARCHAR(4) columns, the values retrieved
from the columns are not always the same because trailing spaces
are removed from CHAR columns upon retrieval.
The following example illustrates this difference:
mysql> CREATE TABLE vc (v VARCHAR(4), c CHAR(4));
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO vc VALUES ('ab ', 'ab ');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT CONCAT('(', v, ')'), CONCAT('(', c, ')') FROM vc;
+---------------------+---------------------+
| CONCAT('(', v, ')') | CONCAT('(', c, ')') |
+---------------------+---------------------+
| (ab ) | (ab) |
+---------------------+---------------------+
1 row in set (0.06 sec)
Values in CHAR and VARCHAR
columns are sorted and compared according to the character set
collation assigned to the column.
Note that all MySQL collations are of type
PADSPACE. This means that all
CHAR and VARCHAR values in
MySQL are compared without regard to any trailing spaces. For
example:
mysql> CREATE TABLE names (myname CHAR(10), yourname VARCHAR(10));
Query OK, 0 rows affected (0.09 sec)
mysql> INSERT INTO names VALUES ('Monty ', 'Monty ');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT myname = 'Monty ', yourname = 'Monty ' FROM names;
+--------------------+----------------------+
| myname = 'Monty ' | yourname = 'Monty ' |
+--------------------+----------------------+
| 1 | 1 |
+--------------------+----------------------+
1 row in set (0.00 sec)
Note that this is true for all MySQL versions, and that it is
not affected by the server SQL mode.
For those cases where trailing pad characters are stripped or
comparisons ignore them, if a column has an index that requires
unique values, inserting into the column values that differ only
in number of trailing pad characters will result in a
duplicate-key error. For example, if a table contains
'a', an attempt to store
'a ' causes a duplicate-key error.
