(mysql.info.gz) CHAR
Info Catalog
(mysql.info.gz) String types
(mysql.info.gz) String types
(mysql.info.gz) BINARY VARBINARY
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.
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 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 from 0 to 255, just as for
`CHAR' columns. (Before MySQL 4.0.2, the length of `VARCHAR' may be
from 1 to 255.) 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
standard SQL specification.
No lettercase 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.
If you need a column for which trailing spaces are not removed,
consider using a `BLOB' or `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
`CHAR' or `VARCHAR' column, to avoid potential problems with trailing
space removal that would change data values.
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:
*Value* `CHAR(4)' *Storage `VARCHAR(4)'*Storage
Required* Required*
`''' `' '' 4 bytes `''' 1 byte
`'ab'' `'ab '' 4 bytes `'ab'' 3 bytes
`'abcd'' `'abcd'' 4 bytes `'abcd'' 5 bytes
`'abcdefgh''`'abcd'' 4 bytes `'abcd'' 5 bytes
The values retrieved from the `CHAR(4)' and `VARCHAR(4)' columns are
the same in each case, because trailing spaces are removed from `CHAR'
columns upon retrieval.
As of MySQL 4.1, values in `CHAR' and `VARCHAR' columns are sorted and
compared according to the collation of the character set 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 `BINARY' attribute to cause sorting and comparison to be case
sensitive using the underlying character code values rather then a
lexical ordering. `BINARY' doesn't affect how the column is stored or
retrieved.
From MySQL 4.1.0 on, 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 treated as a
`BINARY' value.
From MySQL 4.1.0 on, the `ASCII' attribute can be specified for `CHAR'.
It assigns the `latin1' character set.
From MySQL 4.1.1 on, the `UNICODE' attribute can be specified for
`CHAR'. It assigns the `ucs2' character set.
MySQL may silently change the type of a `CHAR' or `VARCHAR' column at
table creation time. Silent column changes.
Info Catalog
(mysql.info.gz) String types
(mysql.info.gz) String types
(mysql.info.gz) BINARY VARBINARY
automatically generated byinfo2html