Skip navigation links

Additional languages
9.1.3.5 Character String Literal Character Set and Collation »
Section Navigation      [Toggle]

9.1.3.4. Column Character Set and Collation

Every “character” column (that is, a column of type CHAR, VARCHAR, or TEXT) 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]

Examples: 

CREATE TABLE Table1
(
    column1 VARCHAR(5) CHARACTER SET latin1 COLLATE latin1_german1_ci
);

ALTER TABLE Table1 MODIFY
    column1 VARCHAR(5) CHARACTER SET latin1 COLLATE latin1_swedish_ci;

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

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

  • If both CHARACTER SET X and COLLATE Y were specified, then character set X and collation Y are used.

  • If CHARACTER SET X was specified without COLLATE, then character set X and its default collation are used.

  • If COLLATE Y was specified without CHARACTER SET, then the character set associated with Y and collation Y.

  • Otherwise, the table character set and collation are used.

The CHARACTER SET and COLLATE clauses are standard SQL.


User Comments

Posted by Drew Folta on December 18 2005 7:51pm[Delete] [Edit]

To change the character set (and collation) for all columns in an existing table, use...
ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];

Posted by Dino Tsoumakis on April 25 2008 3:31pm[Delete] [Edit]

ENUM and SET data types also have a column character set.

Add your own comment.