(mysql.info) char

 
 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. As of MySQL 5.0.3, they also differ in
 maximum length and in whether trailing spaces are retained.
 
 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 255 before MySQL 5.0.3, and 0 to
 65,535 in 5.0.3 and later versions.  (The maximum effective length of a
 `VARCHAR' in MySQL 5.0.3 and later 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. Handling of
 trailing spaces is version-dependent. As of MySQL 5.0.3, trailing
 spaces are retained when values are stored and retrieved, in
 conformance with standard SQL. Before MySQL 5.0.3, trailing spaces are
 removed from values when they are stored into a `VARCHAR' column; this
 means that the spaces also are absent from retrieved values.
 
 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  server-sql-mode.
 
 Before MySQL 5.0.3, if you need a data type for which trailing spaces
 are not removed, consider using a `BLOB' or `TEXT' type. Also, 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 `CHAR' and
 `VARCHAR' 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
 
 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 it makes no
 difference whether your version trims trailing spaces from `VARCHAR'
 values before storing them. Nor does the server SQL mode make any
 difference in this regard.
 
 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.
Info Catalog
(mysql.info) string-types
(mysql.info) binary-varbinary
automatically generated byinfo2html