GNU Info

(mysql.info)CHAR

---

Next: BLOB Prev: String types Up: String types

---

Enter node , (file) or (file)node

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 ANSI SQL specification.)

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 table below 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 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.

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.  Note: Silent column changes.