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. (Before MySQL 4.1, the length is interpreted
as number of bytes.)
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. (Before MySQL 3.23,
the length of
CHAR may be from 1
to 255.) When
CHAR values are
stored, they are right-padded with spaces to the specified
CHAR values are
retrieved, trailing spaces are removed.
VARCHAR columns are
variable-length strings. The length can be specified as a value
from 1 to 255 before MySQL 4.0.2 and 0 to 255 as of MySQL 4.0.2.
VARCHAR values are not padded
when they are stored. Trailing spaces in MySQL version up to and
including 4.1 are removed from values when stored in a
VARCHAR column; this also means
that the spaces are absent from retrieved values.
If you need a data type for which trailing spaces are not
removed, consider using a
TEXT type. If you want to store
binary values such as results from an encryption or compression
function that might contain arbitrary byte values, use a
BLOB column rather than a
VARCHAR column, to avoid
potential problems with trailing space removal that would change
The following table illustrates the differences between
VARCHAR by showing the result of
storing various string values into
VARCHAR(4) columns (assuming that the
column uses a single-byte character set such as
If a given value is stored into the
VARCHAR(4) columns, the values retrieved
from the columns are not always the same because trailing spaces
are removed from
As of MySQL 4.1, values in
VARCHAR columns are sorted
and compared according to the character set collation assigned
to the column. Before MySQL 4.1, sorting and comparison are
based on the collation of the server character set; you can
declare the column with the
to cause sorting and comparison to be based on the numeric
values of the bytes in column values.
does not affect how column values are stored or retrieved.
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)
This is true for all MySQL versions, and it is not affected by
the trimming of trailing spaces from
VARCHAR values before storing
them. Nor does the server SQL mode make any difference in this
For more information about MySQL character sets and collations, see Section 9.1, “Character Set Support”.
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.
BINARY attribute is sticky. This means
that if a column marked
BINARY is used in an
expression, the whole expression is treated as a
MySQL may silently change the type of a
VARCHAR column at table creation
time. See Section 18.104.22.168, “Silent Column Specification Changes”.