Skip navigation links

Additional languages
9.1.3.4 Column Character Set and Collation »
Section Navigation      [Toggle]

9.1.3.3. Table Character Set and Collation

Every table has a table character set and a table collation. The CREATE TABLE and ALTER TABLE statements have optional clauses for specifying the table character set and collation: 

CREATE TABLE tbl_name (column_list)
    [[DEFAULT] CHARACTER SET charset_name] [COLLATE collation_name]]

ALTER TABLE tbl_name
    [[DEFAULT] CHARACTER SET charset_name] [COLLATE collation_name]

Example: 

CREATE TABLE t1 ( ... ) CHARACTER SET latin1 COLLATE latin1_danish_ci;

MySQL chooses the table 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.

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

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

  • Otherwise, the database character set and collation.

The table character set and collation are used as default values if the column character set and collation are not specified in individual column definitions. The table character set and collation are MySQL extensions; there are no such things in standard SQL.


User Comments

Posted by brusselsshrek on August 5 2005 2:22pm[Delete] [Edit]

I noticed that my error log file was filled with the following messages (after upgrade to 4.1.12, though I don't look at log files often so it may have been there before). I found clues from other posts on the internet which helped me solve it, and I post my complete solution here in the hope it might help someone else.

050805 15:30:06 [Warning] './localpinprod/mytablename' had no or invalid character set, and default character set is multi-byte, so character column sizes may have changed

I performed a "FLUSH LOGS;" to try and be clear where the error was coming from.

The error occurs at times I'm not clear of, but to try and find an easily reproducible test, I performed a "REPAIR TABLE `mytablename`" just to try and force the error, and true enough, the error log added the same error.

I then did an "ALTER TABLE mytablename DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci"

Now when I do a "REPAIR TABLE `mytablename`" I do not get the error. I did another "FLUSH LOGS" first to make really sure that nothing was missed.

Hope this helps someone.

Add your own comment.