Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 39.3Mb
PDF (A4) - 39.3Mb
PDF (RPM) - 38.4Mb
HTML Download (TGZ) - 10.9Mb
HTML Download (Zip) - 11.0Mb
HTML Download (RPM) - 9.6Mb
Man Pages (TGZ) - 217.9Kb
Man Pages (Zip) - 327.6Kb
Info (Gzip) - 3.6Mb
Info (Zip) - 3.6Mb
Excerpts from this Manual

10.3.5 Column Character Set and Collation

Every character column (that is, a column of type CHAR, VARCHAR, a TEXT type, or any synonym) has a column character set and a column collation. Column definition syntax for CREATE TABLE and ALTER TABLE has optional clauses for specifying the column character set and collation:

col_name {CHAR | VARCHAR | TEXT} (col_length)
    [CHARACTER SET charset_name]
    [COLLATE collation_name]

These clauses can also be used for ENUM and SET columns:

col_name {ENUM | SET} (val_list)
    [CHARACTER SET charset_name]
    [COLLATE collation_name]

Examples:

CREATE TABLE t1
(
    col1 VARCHAR(5)
      CHARACTER SET latin1
      COLLATE latin1_german1_ci
);

ALTER TABLE t1 MODIFY
    col1 VARCHAR(5)
      CHARACTER SET latin1
      COLLATE latin1_swedish_ci;

MySQL chooses the column character set and collation in the following manner:

  • If both CHARACTER SET charset_name and COLLATE collation_name are specified, character set charset_name and collation collation_name are used.

    CREATE TABLE t1
    (
        col1 CHAR(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci
    ) CHARACTER SET latin1 COLLATE latin1_bin;

    The character set and collation are specified for the column, so they are used. The column has character set utf8 and collation utf8_unicode_ci.

  • If CHARACTER SET charset_name is specified without COLLATE, character set charset_name and its default collation are used.

    CREATE TABLE t1
    (
        col1 CHAR(10) CHARACTER SET utf8
    ) CHARACTER SET latin1 COLLATE latin1_bin;

    The character set is specified for the column, but the collation is not. The column has character set utf8 and the default collation for utf8, which is utf8_general_ci. To see the default collation for each character set, use the SHOW CHARACTER SET statement or query the INFORMATION_SCHEMA CHARACTER_SETS table.

  • If COLLATE collation_name is specified without CHARACTER SET, the character set associated with collation_name and collation collation_name are used.

    CREATE TABLE t1
    (
        col1 CHAR(10) COLLATE utf8_polish_ci
    ) CHARACTER SET latin1 COLLATE latin1_bin;

    The collation is specified for the column, but the character set is not. The column has collation utf8_polish_ci and the character set is the one associated with the collation, which is utf8.

  • Otherwise (neither CHARACTER SET nor COLLATE is specified), the table character set and collation are used.

    CREATE TABLE t1
    (
        col1 CHAR(10)
    ) CHARACTER SET latin1 COLLATE latin1_bin;

    Neither the character set nor collation is specified for the column, so the table defaults are used. The column has character set latin1 and collation latin1_bin.

The CHARACTER SET and COLLATE clauses are standard SQL.

If you use ALTER TABLE to convert a column from one character set to another, MySQL attempts to map the data values, but if the character sets are incompatible, there may be data loss.